使用 LEFT JOIN 更新 MySQL 中的多个表

UPDATE multiple tables in MySQL using LEFT JOIN

提问by Paul Oyster

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

我有两个表，想更新 T1 中左连接中所有行的字段。

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 manualstates that:

在MySQL手册指出：

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

多表 UPDATE 语句可以使用 SELECT 语句中允许的任何类型的连接，例如 LEFT JOIN。

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

但是我在记录的多表更新中找不到这样做的正确语法。

What is the proper syntax?

什么是正确的语法？

回答by Quassnoi

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

Note that for a SELECTit would be more efficient to use NOT IN/ NOT EXISTSsyntax:

请注意，对于 aSELECT使用NOT IN/NOT EXISTS语法会更有效：

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 JOINcompared to NOT IN

• 查找不完整的订单：LEFT JOIN与NOT IN

Unfortunately, MySQLdoes not allow using the target table in a subquery in an UPDATEstatement, that's why you'll need to stick to less efficient LEFT JOINsyntax.

不幸的是，MySQL不允许在UPDATE语句的子查询中使用目标表，这就是为什么您需要坚持使用效率较低的LEFT JOIN语法。

回答by theprivileges

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.

这在您有用户和组的情况下很有用，并且您希望用户能够添加他们自己的组名变体，因此最初您希望将现有组名导入到用户所在的字段中将能够修改它。

回答by Dinesh Rabara

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

我将更新表 A 中的字段文本

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:

只接受表 B 中的一个字段，但我会得出以下结果：

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

回答by guest

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`)
)

回答by Shyam Sharma

                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)