如何使用mysql join更新记录? [英] How to update record using mysql join?

问题描述

在我的mysql中，我拥有t1，t2表，并且我想根据t1的字段值与t2的字段值匹配，从t2的字段值更新t1的字段

In my mysql I am having t1, t2 tables and I want to update t1's field from t2's field value based on t1's field value match t2's field value

我尝试了以下操作，但未更新.我在这里做错了

I tried the below but it is not updating. What I did wrongly here

UPDATE t1 
INNER JOIN t2 
ON t1.name = t2.name 
SET t1.age = t2.age 
WHERE t1.name IS NOT NULL;

推荐答案

您需要将要更新的表与查询的表分开，即使它是相同的:

You need to separate the table you want to update from the table your querying, even though it is the same:

UPDATE t1  
SET t1.age = t2.age
FROM t1 as tempT1
INNER JOIN t2 
ON tempT1.name = t2.name 
WHERE tempT1.name IS NOT NULL;

更新
显然，MySQL使用的UPDATE JOIN语法与其他数据库的语法不同.您的初始查询似乎使用了正确的语法，只是为了确保尝试为表名加上别名:

UPDATE
Apparently MySQL is using a different UPDATE JOIN Syntax than other db's. Your initial query seems to use the correct syntax, just to be sure try to alias the table names:

UPDATE t1 temp1
INNER JOIN t2 temp2
ON temp1.name = temp2.name 
SET temp1.age = temp2.age 
WHERE temp1.name IS NOT NULL;

更新2
在看了更长的时间后，我确定WHERE子句是问题所在:
WHERE temp1.name IS NOT NULL
无论如何，您都无法加入空值，因此默认情况下会将它们过滤掉. WHERE子句以某种方式干扰了联接.
尝试将其删除，以查看UPDATE是否有效.如果您不想立即执行和更新，只需使用相同的JOIN CLAUSE执行选择以查看哪些记录将受到影响.

UPDATE 2
After looking at this a bit longer I'm certain that the WHERE clause is the issue:
WHERE temp1.name IS NOT NULL
You cannot join on null values anyway, so they are filtered out by default. The WHERE clause is somehow interfering with the join.
Try and remove it to see if the UPDATEworks. If you don' t want to execute and update right away simply execute a select with the same JOIN CLAUSE to see which records would be affected.

此处是对NULL和JOIN的一般引用:
http://databases.about.com/library/weekly/aa051203a.htm

Here is a general reference to NULL and JOIN:
http://databases.about.com/library/weekly/aa051203a.htm

以下是符合上述条件的SQL Server参考: http://msdn.microsoft.com/en-us/library/ms190409.aspx

Here is the SQL Server Reference in compliance with the above: http://msdn.microsoft.com/en-us/library/ms190409.aspx

找不到一个明确指出这一点的MySQL引用，但我认为这对于所有关系数据库都是正确的.

Could not find a MySQL reference that states this explicitly but I think this is true for all Relational DBs.

这篇关于如何使用mysql join更新记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！