mysql更新where子句子查询中的2列

Question

在下表中，我需要更新列 brake_pad，如下所示:

Table ct_maint(simplified). 
Primary key is the combined model_id/fuel columns.

model_id    fuel    brake_pad
   1        gas         0
   1        diesel      0
   2        gas         0
   3        diesel      0
   4        gas         0
   4        diesel      0
   5        diesel      0
   6        gas         0
   6        diesel      0  
   ......

告诉我要更新哪个 brake_pad 的查询是:

SELECT models.model_id, motors.fuel FROM models
INNER JOIN versions USING(model_id)
INNER JOIN versiontrim USING(version_id)
INNER JOIN motors USING(motor_id)
WHERE trim_id IN(502, 506)
GROUP BY model_id, fuel

此查询给出的结果集如下:

model_id      fuel
   1          diesel
   3          diesel
   4          gas
   4          diesel
   6          gas

所以，我想，

UPDATE ct_maint SET brake_pad=1000 WHERE model_id AND fuel IN( SELECT.....)

但在 mysql 提醒我之前很久就停止了。

感谢任何帮助。

Answer 1

试试这个查询:

UPDATE ct_maint SET brake_pad=1000 WHERE (model_id, fuel) IN ( SELECT.....)