对于每个列出的值，MySQL UPDATE分别放在哪里? [英] MySQL UPDATE WHERE IN for each listed value separately?

问题描述

我有以下类型的SQL:

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)

基本上，两个表(car& photo)是相关的. $ ids中的列表包含唯一的照片ID，例如(34，87，98，12).通过查询，我将photo表中该列表中每张照片的状态设置为"1"，同时为手边的汽车在car表中增加照片计数.

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.

它可以工作，但有一个障碍:因为列表中可以包含与同一辆车相关的多个照片ID，所以照片计数只会增加一次.如果列表中有10张与同一辆车相关的照片，则photo_count会变成1 ....而我想将其增加到10张.

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.

相对于MySQL，我是否有办法通过联接使每张照片分别递增?

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

这篇关于对于每个列出的值，MySQL UPDATE分别放在哪里?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！