MySQL 在 SQL 中，如何通过查找列的所有行来更新表的每一行，然后将另一列设置为彼此相等

In SQL how do you update each row of the table by finding all rows that are equal for a column, then set another column equal to eachother

提问by hthomos

So basically this would be the psuedo code, but I don't know how to do this in SQL, please help.

所以基本上这将是伪代码，但我不知道如何在 SQL 中做到这一点，请帮忙。

for each row in table1{
    loop through each row in table 2 {
        if table1's row.column 1 = table2's row.column 2 for this row {
            set table1's row.col2 = table2's row.col2
        }
    }
}

Edit: Okay let me be more specific. We are basically switching from hibernate sequence as ids to using guids for the id column. I'm trying to update the foreign keys associated by making a temp of the previous foreign key column and then matching the temporary columns to update the actual columns.

编辑：好的，让我更具体一点。我们基本上是从休眠序列作为 id 切换到使用 id 列的 guid。我正在尝试通过创建前一个外键列的临时值然后匹配临时列来更新实际列来更新关联的外键。

suppose table one had id's and table two had a column for those ids to use as foreign keys. I wanna use the previous values in table 2 to match with the rows in table 1 and set the key values in table 2 to match the new guid of table 1.

假设表一有 id ，表二有一个列，这些 id 用作外键。我想使用表 2 中以前的值与表 1 中的行匹配，并设置表 2 中的键值以匹配表 1 的新 guid。

so table 2 may have multiple rows of duplicate id's but table 1 will never have duplicates. If that makes any sense.

所以表 2 可能有多行重复的 id，但表 1 永远不会有重复。如果那有意义的话。

回答by Laurent PerrucheJ

In SQL Server you can do something like:

在 SQL Server 中，您可以执行以下操作：

UPDATE Table_1
SET Column_2 = t2.Column_2
FROM Table_1 AS t1
INNER JOIN Table_2 AS t2 ON t2.Column_1 = t1.Column_1

or something like

或类似的东西

UPDATE Table_1
SET Column_2 = ( 
    SELECT t2.Column_2
    FROM Table_2 AS t2
    WHERE t2.Column_1 = Table_1.Column_1
)

Of course if you have multiple rows in Table_2, you will get an error....

当然如果你在Table_2中有多行，你会得到一个错误....

回答by Damien_The_Unbeliever

The basics of it is:

它的基本原理是：

UPDATE Table1 SET col2 =
     (select col2 FROM Table2 where Table2.column2 = Table1.column1)

But that may not do exactly what you need if there's not a 1-1 correspondence between rows in the two tables - and hence my current comment below your question:

但是，如果两个表中的行之间没有 1-1 对应关系，那可能无法完全满足您的需求 - 因此我在您的问题下方发表了当前评论：

What should happen if there are more than one matching row in table 2? What should happen if there's no matching row?

如果表 2 中有多个匹配行会发生什么情况？如果没有匹配的行会发生什么？