在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

问题描述

所以基本上这是伪代码，但我不知道如何在SQL中执行此操作，请帮忙.

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

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

好的，让我更具体一些.我们基本上是从休眠序列作为id切换到对id列使用guids.我正在尝试通过使上一个外键列成为临时文件，然后匹配临时列以更新实际列来更新关联的外键.

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用作外键.我想使用表2中的先前值与表1中的行进行匹配，并在表2中设置键值以匹配表1的新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.

因此表2可能有多行重复的ID，但表1永远不会有重复的ID.如果有任何意义.

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

推荐答案

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

In SQL Server you can do something like:

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

或类似的

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

当然，如果Table_2中有多行，则会出现错误....

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

这篇关于在SQL中，如何查找表中的每一行都等于一列，然后再设置另一列彼此相等，从而更新表的每一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！