[FIXED] How to write an UPDATE depending on values in other table

Issue

I have a question concerning "UPDATE" in MySQL. I have two tables table1 and table2 which are connected table1.id=table2.tab1. Now, I'd like to update a value in table2 depending on a value in table1. I was trying to do it like this:

UPDATE table2 SET table2.val='new value' 
WHERE table1.id=table2.tab1 AND table1.val='xy' 

In other words, I would like to change the value val for all entries in table2 where the corresponding entry in table1 has the value 'xy'.

Unfortunately, this doesn't work. Can someone give me a hint?

Solution

This isn't the correct syntax. You should use an update-join

UPDATE table2
JOIN   table1 ON table1.id = table2.tab1
SET    table2.val = 'new value' 
WHERE  table1.val = 'xy' 

Answered By - Mureinik

Answer Checked By - Senaida (PHPFixing Volunteer)