MySQL UPDATE WHERE IN for each listed value separately?
- by Tom
Hi,
I've got the following type of SQL:
UPDATE photo AS f
LEFT JOIN car AS c
ON f.car_id=c.car_id
SET f.photo_status=1
, c.photo_count=c.photo_count+1
WHERE f.photo_id IN ($ids)
Basically, two tables (car & photo) are related. The list in $ids contains unique photo ids, such as (34, 87, 98, 12). With the query, I'm setting the status of each photo in that list to "1" in the photo table and simultaneously incrementing the photo count in the car table for the car at hand.
It works but there's one snag: Because the list can contain multiple photo ids that relate to the same car, the photo count only ever gets incremented once. If the list had 10 photos associated with the same car, photo_count would become 1 .... whereas I'd like to increment it to 10.
Is there a way to make the incrementation occur for each photo individually through the join, as opposed to MySQL overthinking it for me?
I hope the above makes sense. Thanks.
