UPDATE multiple tables in MySQL using LEFT JOIN

I have two tables, and want to update fields in T1 for all rows in a LEFT JOIN.

For an easy example, update all rows of the following result-set:

SELECT T1.* FROM T1 LEFT JOIN T2 ON T1.id = T2.id WHERE T2.id IS NULL  

The MySQL manual states that:

Multiple-table UPDATE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN.

But I cannot find the proper syntax for doing that in the documented multiple-tables UPDATE.

What is the proper syntax?

 Answers

UPDATE  t1
LEFT JOIN
        t2
ON      t2.id = t1.id
SET     t1.col1 = newvalue
WHERE   t2.id IS NULL

Note that for a SELECT it would be more efficient to use NOT IN / NOT EXISTS syntax:

SELECT  t1.*
FROM    t1
WHERE   t1.id NOT IN
        (
        SELECT  id
        FROM    t2
        )

See the article in my blog for performance details:

• Finding incomplete orders: performance of LEFT JOIN compared to NOT IN

Unfortunately, MySQL does not allow using the target table in a subquery in an 

UPDATE statement, that's why you'll need to stick to less efficient LEFT JOIN syntax.

---

Table A 
+--------+-----------+
| A-num  | text      | 
|    1   |           |
|    2   |           |
|    3   |           |
|    4   |           |
|    5   |           |
+--------+-----------+

Table B
+------+------+--------------+
| B-num|  date        |  A-num | 
|  22  |  01.08.2003  |     2  |
|  23  |  02.08.2003  |     2  | 
|  24  |  03.08.2003  |     1  |
|  25  |  04.08.2003  |     4  |
|  26  |  05.03.2003  |     4  |

I will update field text in table A with

UPDATE `Table A`,`Table B`
SET `Table A`.`text`=concat_ws('',`Table A`.`text`,`Table B`.`B-num`," from                                           
",`Table B`.`date`,'/')
WHERE `Table A`.`A-num` = `Table B`.`A-num`

and come to this result:

Table A 
+--------+------------------------+
| A-num  | text                   | 
|    1   |  24 from 03 08 2003 /  |
|    2   |  22 from 01 08 2003 /  |       
|    3   |                        |
|    4   |  25 from 04 08 2003 /  |
|    5   |                        |
--------+-------------------------+

where only one field from Table B is accepted, but I will come to this result:

Table A 
+--------+--------------------------------------------+
| A-num  | text                                       | 
|    1   |  24 from 03 08 2003                        |
|    2   |  22 from 01 08 2003 / 23 from 02 08 2003 / |       
|    3   |                                            |
|    4   |  25 from 04 08 2003 / 26 from 05 03 2003 / |
|    5   |                                            |
+--------+--------------------------------------------+

---

DECLARE @cols VARCHAR(max),@colsUpd VARCHAR(max), @query VARCHAR(max),

@queryUpd VARCHAR(max), @subQuery VARCHAR(max)

DECLARE @TableNameTest NVARCHAR(150)

SET @TableNameTest = @TableName+ '_Staging';

                 WHERE object_id = (

SELECT  @colsUpd = STUF  ((SELECT DISTINCT '], 

T1.[' + name,']=T2.['+name+'' FROM sys.columns
SELECT top 1 object_id and name not in ('Action','Record_ID')  FROM sys.objects

WHERE name = ''+@TableNameTest+''
)
                FOR XML PATH('')

EXEC (@queryUpd)

            ), 1, 2, ''
) + ']'



  Select @queryUpd ='Update T1
SET '+@colsUpd+'
FROM '+@TableName+' T1

ON T1.Record_ID = T2.Record_Id
INNER JOIN '+@TableNameTest+' T2

WHERE T2.[Action] = ''Modify'''