MySQL UPDATE WHERE IN for each listed value separa

Question

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.

Answer 1

What I think you should do:

update the tables in two steps. First set the status to 1 in photo table, then update the count (by using group by and count(...) ) to car table.

EDIT Retracted the 'naive approach' , won't work as the OP correctly states!

Answer 2

You can do this with two queries:

UPDATE car
SET photo_count = photo_count + (
    SELECT count(*)
    FROM photos
    WHERE photo.car_id = car.car_id
    AND photo_status = 0
    AND photo_id IN ($ids)
);

UPDATE photo
SET photo_status = 1
WHERE photo_id IN ($ids);