CODEWITHSUNDEEP
mysqlsql
jhall1990
jhall1990

Reputation: 151

Update row with data from another table

I have a mysql database with the following tables.

driver table

  id    name    vehicleId
 -------------------------
   1     bob            1
   2     jim            1
   3     rob            2
   4     tim            2
   5     sue            3

vehicle table

  id      model
 ---------------
   1      civic
   2       cr-v
   3      camry
   4    corolla

I want to be able to update a given driver's vehicle using the vehicles model.

I was able to get this working using,

UPDATE driver, vehicle
SET vehicleId = vehicle.id
WHERE driver.name = "bob"
AND vehicle.model = "corolla";

But that doesn't seem like the best/right solution. From what I've read it seems like I should be able to do this using a join, but I haven't had any luck using that method.

Is a join based solution better or is my solution okay?

Thanks in advance!

Upvotes: 1

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Your formulation is fine, although I much prefer CROSS JOIN to ,.

You can also express this using a subquery:

UPDATE driver 
    SET vehicleId = (SELECT v.id FROM vehicle v WHERE v.model = 'corolla')
    WHERE name = 'bob';

Upvotes: 1

Related Questions