mysql update 所有字段的值,MySQL UPDATE WHERE IN分别为每个列出的值？

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.

解决方案

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);