CODEWITHSUNDEEP
phpmysqljoin
Rob
Rob

Reputation: 1539

Update statement with join

I'm trying to update a field in 2 tables with the same value - bookings(tbl) - dropOffLocation and cars(tbl) - currentbranch.

I can get the desired result with 2 update statements such as the ones below:

UPDATE bookings b SET b.dropOffLocation = 'London' WHERE b.regNumber = 'AX03PFF'
UPDATE cars c SET c.currentBranch = 'London' WHERE c.regNumber = 'AX03PFF'

However, I'd like to combine the 2 update statements into one using a JOIN. I've tried the sql below but it isn't doing anything.

UPDATE 
  bookings b JOIN cars c
SET 
  b.dropOffLocation = 'London' 
  AND c.currentBranch = 'London' 
WHERE b.regNumber = 'EP59YMP' AND c.regNumber = 'EP59YMP'

Can someone please help me to amend the join statement to get it working.

Thanks!

Upvotes: 1

Views: 891

Answers (3)

Ignas
Ignas

Reputation: 1965

Try this:

UPDATE FROM bookings AS b
LEFT JOIN cars AS c ON c.regNumber = b.regNumber
SET b.dropOffLocation = 'London' AND c.currentBranch = 'London' 
WHERE b.regNumber = 'EP59YMP'

Upvotes: 0

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36631

UPDATE 
bookings b JOIN cars c
on b.regNumber = c.regNumber

SET 
b.dropOffLocation = 'London', 
AND c.currentBranch = 'London',

WHERE b.regNumber = 'EP59YMP' AND c.regNumber = 'EP59YMP';

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270637

Your syntax is a little off. Use , instead of AND inside an UPDATE SET clause. Instead of placing both tables into the WHERE clause, I have added an equivalent ON clause to the JOIN, which is a bit more readable in that it directly states the relationship between the tables.

UPDATE 
  bookings b JOIN cars c ON b.regNumber = c.regNumber
SET 
  b.dropOffLocation = 'London',
  c.currentBranch = 'London'
WHERE b.regNumber = 'EP59YMP'

Upvotes: 3

Related Questions