UPDATE multiple tables in MySQL using LEFT JOIN

Question

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?

Answer 1

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.

Answer 2

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   |                                            |
+--------+--------------------------------------------+

Answer 3

UPDATE `Table A` a
SET a.`text`=(
        SELECT group_concat(b.`B-num`,' from ',b.`date` SEPARATOR ' / ') 
        FROM `Table B` b WHERE (a.`A-num`=b.`A-num`)
)

Answer 4

The same can be applied to a scenario where the data has been normalized, but now you want a table to have values found in a third table. The following will allow you to update a table with information from a third table that is liked by a second table.

UPDATE t1
LEFT JOIN
 t2
ON 
 t2.some_id = t1.some_id
LEFT JOIN
 t3 
ON
 t2.t3_id = t3.id
SET 
 t1.new_column = t3.column;

This would be useful in a case where you had users and groups, and you wanted a user to be able to add their own variation of the group name, so originally you would want to import the existing group names into the field where the user is going to be able to modify it.

Answer 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';
SELECT  @colsUpd = STUF  ((SELECT DISTINCT '], T1.[' + name,']=T2.['+name+'' FROM sys.columns
                 WHERE object_id = (
                                    SELECT top 1 object_id 
                                      FROM sys.objects
                                     WHERE name = ''+@TableNameTest+''
                                    )
                and name not in ('Action','Record_ID')
                FOR XML PATH('')
            ), 1, 2, ''
        ) + ']'


  Select @queryUpd ='Update T1
SET '+@colsUpd+'
FROM '+@TableName+' T1
INNER JOIN '+@TableNameTest+' T2
ON T1.Record_ID = T2.Record_Id
WHERE T2.[Action] = ''Modify'''
EXEC (@queryUpd)