Update statement with join

Question

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!

See this question - http://stackoverflow.com/questions/4641970/how-do-i-combine-two-update-statements-in-one-mysql-query — Jody, Mar 11 '12 at 13:41
By saying _isn't doing anything_, along with the PHP tag, we suspect you aren't error-checking your query executions. If using `mysql_query()`, please `echo mysql_error()` on query failure to see what went wrong. — Michael Berkowski, Mar 11 '12 at 14:01

score 3 · Accepted Answer · answered Mar 11 '12 at 13:39

3

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'

answered Mar 11 '12 at 13:39

Michael Berkowski

267,341
46
444
390

+1. But I think the bigger problem is that the OP doesn't appear to be checking for SQL errors, such that a syntactically invalid SQL statement simply "isn't doing anything". – ruakh Mar 11 '12 at 13:41

score 0 · Answer 2 · answered Mar 11 '12 at 13:41

0

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

answered Mar 11 '12 at 13:41

Vishwanath Dalvi

35,388
41
123
155

score 0 · Answer 3 · answered Mar 11 '12 at 13:48

0

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'

answered Mar 11 '12 at 13:48

Ignas

1,965
2
17
44

Update statement with join

3 Answers3