CODEWITHSUNDEEP
mysql
BernardA
BernardA

Reputation: 1523

mysql update with 2 columns in where clause subquery

In the table below, I need to update column brake_pad, as follows:

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  
   ......

The query that tells me which brake_pad to update is:

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

This query gives a result set like:

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

So, I was going like,

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

but stopped long before mysql buzzed me.

Any help is appreciated.

Upvotes: 1

Views: 69

Answers (2)

Stefan Winkler
Stefan Winkler

Reputation: 3956

There are two approaches which you can use alternatively:

1) I think you can use tuples for IN:

UPDATE ct_maint SET brake_pad=1000 
WHERE (model_id, fuel) IN 
   (SELECT models.model_id, motors.fuel FROM models WHERE ... );

2) You can use joins in updates

UPDATE ct_maint SET brake_pad=1000 
INNER JOIN (SELECT ...) models_upd
ON ct_maint.model_id = models_upd.model_id
AND ct_maint.fuel = models_upd.fuel;

Upvotes: 2

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

Try this query:

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

Upvotes: 2

Related Questions