Search code examples
mysqlsqljoinsyntaxsql-update

How to write an UPDATE depending on values in other table

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'