基于ID匹配从一个表更新到另一个表

I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number, so that I am only working with account numbers.

我有一个有帐号和卡号的数据库。我将它们匹配到一个文件中，以便将任何卡号更新到账号中，因此我只处理账号。

I created a view linking the table to the account/card database to return the Table ID and the related account number, and now I need to update those records where the ID matches with the Account Number.

我创建了一个连接表到account/card数据库的视图，以返回表ID和相关的帐户号，现在我需要更新ID与帐户号匹配的记录。

This is the Sales_Import table, where the account number field needs to be updated:

这是Sales_Import表，其中需要更新账号字段:

LeadID  AccountNumber
147         5807811235
150         5807811326
185         7006100100007267039

And this is the RetrieveAccountNumber table, where I need to update from:

这是RetrieveAccountNumber表，我需要从以下内容进行更新:

LeadID  AccountNumber
147         7006100100007266957
150         7006100100007267039

I tried the below, but no luck so far:

我试过下面的方法，但到目前为止还没有成功:

UPDATE [Sales_Lead].[dbo].[Sales_Import] 
SET    [AccountNumber] = (SELECT RetrieveAccountNumber.AccountNumber 
                          FROM   RetrieveAccountNumber 
                          WHERE  [Sales_Lead].[dbo].[Sales_Import]. LeadID = 
                                                RetrieveAccountNumber.LeadID) 

It updates the card numbers to account numbers, but the account numbers gets replaced by NULL

它将卡号更新为帐号，但是帐号被NULL替换

19 个解决方案

#1

I believe an UPDATE FROM with a JOIN will help:

我相信一个来自JOIN的更新将会有所帮助:

MS SQL

UPDATE
    Sales_Import
SET
    Sales_Import.AccountNumber = RAN.AccountNumber
FROM
    Sales_Import SI
INNER JOIN
    RetrieveAccountNumber RAN
ON 
    SI.LeadID = RAN.LeadID;

MySQL and MariaDB

UPDATE
    Sales_Import SI,
    RetrieveAccountNumber RAN
SET
    SI.AccountNumber = RAN.AccountNumber
WHERE
    SI.LeadID = RAN.LeadID;

#2

The simple Way to copy the content from one table to other is as follow:

将内容从一个表复制到另一个表的简单方法如下:

UPDATE table2 
SET table2.col1 = table1.col1, 
table2.col2 = table1.col2,
...
FROM table1, table2 
WHERE table1.memberid = table2.memberid

You can also add the condition to get the particular data copied.

您还可以添加条件来获取复制的特定数据。

#3

For SQL Server 2008 + Using MERGE rather than the proprietary UPDATE ... FROM syntax has some appeal.

对于SQL Server 2008 +使用MERGE而不是专有更新…语法有一些吸引力。

As well as being standard SQL and thus more portable it also will raise an error in the event of there being multiple joined rows on the source side (and thus multiple possible different values to use in the update) rather than having the final result be undeterministic.

除了是标准的SQL，因此更具可移植性，它还会在源端有多个连接行(因此在更新中可能使用多个不同的值)而不是最终结果不确定的情况下引发错误。

MERGE INTO Sales_Import
   USING RetrieveAccountNumber
      ON Sales_Import.LeadID = RetrieveAccountNumber.LeadID
WHEN MATCHED THEN
   UPDATE 
      SET AccountNumber = RetrieveAccountNumber.AccountNumber;

Unfortunately the choice of which to use may not come down purely to preferred style however. The implementation of MERGE in SQL Server has been afflicted with various bugs. Aaron Bertrand has compiled a list of the reported ones here.

然而，不幸的是，选择使用哪一种风格并不完全取决于偏好的风格。SQL Server中MERGE的实现一直受到各种bug的困扰。Aaron Bertrand在这里整理了一份报告。

#4

Generic answer for future developers.

对未来开发人员的一般回答。

SQL Server

UPDATE 
     t1
SET 
     t1.column = t2.column
FROM 
     Table1 t1 
     INNER JOIN Table2 t2 
     ON t1.id = t2.id;

Oracle (and SQL Server)

UPDATE 
     t1
SET 
     t1.colmun = t2.column 
FROM 
     Table1 t1, 
     Table2 t2 
WHERE 
     t1.ID = t2.ID;

MySQL

UPDATE 
     Table1 t1, 
     Table2 t2
SET 
     t1.column = t2.column 
WHERE
     t1.ID = t2.ID;

#5

Seems you are using MSSQL, then, if I remember correctly, it is done like this:

似乎你在使用MSSQL，那么，如果我没记错的话，它是这样做的:

UPDATE [Sales_Lead].[dbo].[Sales_Import] SET [AccountNumber] = 
RetrieveAccountNumber.AccountNumber 
FROM RetrieveAccountNumber 
WHERE [Sales_Lead].[dbo].[Sales_Import].LeadID = RetrieveAccountNumber.LeadID

#6

I had the same problem with foo.new being set to null for rows of foo that had no matching key in bar. I did something like this in Oracle:

我对foo也有同样的问题。对于在bar中没有匹配键的foo行，将其设置为null。我在Oracle中做过这样的事情:

update foo
set    foo.new = (select bar.new
                  from bar 
                  where foo.key = bar.key)
where exists (select 1
              from bar
              where foo.key = bar.key)

#7

For MySql that works fine:

MySql运行良好:

UPDATE
    Sales_Import SI,RetrieveAccountNumber RAN
SET
    SI.AccountNumber = RAN.AccountNumber
WHERE
    SI.LeadID = RAN.LeadID

#8

For PostgreSQL:

PostgreSQL的:

UPDATE Sales_Import SI
SET AccountNumber = RAN.AccountNumber
FROM RetrieveAccountNumber RAN
WHERE RAN.LeadID = SI.LeadID; 

#9

For MySql:

MySql:

UPDATE table1 JOIN table2 
ON table1.id = table2.id
SET table1.name = table2.name,
table1.`desc` = table2.`desc`

For Sql Server:

Sql服务器:

UPDATE table1
SET table1.name = table2.name,
table1.[desc] = table2.[desc]
FROM table1 JOIN table2 
ON table1.id = table2.id

I have used all above queries, but they're not working; and when I used this one, it's done.

我已经使用了以上所有查询，但它们不起作用;当我用这个的时候，它就完成了。

You can catch it from here also, https://*.com/questions/5036918/update-one-table-with-data-from-another

你也可以从这里找到它，https://*.com/questions/5036918/updateone - withdata - from-data -from- other。

Hope u get it. thanks

希望你得到它。谢谢

#10

Thanks for the responses. I found a solution tho.

谢谢你的回复。我找到了一个解决办法。

UPDATE Sales_Import 
SET    AccountNumber = (SELECT RetrieveAccountNumber.AccountNumber 
                          FROM   RetrieveAccountNumber 
                          WHERE  Sales_Import.leadid =RetrieveAccountNumber.LeadID) 
WHERE Sales_Import.leadid = (SELECT  RetrieveAccountNumber.LeadID 
                             FROM   RetrieveAccountNumber 
                             WHERE  Sales_Import.leadid = RetrieveAccountNumber.LeadID)  

#11

update within the same table:

在同一表内更新:

  DECLARE @TB1 TABLE
    (
        No Int
        ,Name NVarchar(50)
        ,linkNo int
    )

    DECLARE @TB2 TABLE
    (
        No Int
        ,Name NVarchar(50)
        ,linkNo int
    )

    INSERT INTO @TB1 VALUES(1,'changed person data',  0);
    INSERT INTO @TB1 VALUES(2,'old linked data of person', 1);

INSERT INTO @TB2 SELECT * FROM @TB1 WHERE linkNo = 0


SELECT * FROM @TB1
SELECT * FROM @TB2


    UPDATE @TB1 
        SET Name = T2.Name
    FROM        @TB1 T1
    INNER JOIN  @TB2 T2 ON T2.No = T1.linkNo

    SELECT * FROM @TB1

#12

The below SQL someone suggested, does NOT work in SQL Server. This syntax reminds me of my old school class:

下面有人建议的SQL，在SQL Server中不工作。这个句法让我想起了我以前的学校:

UPDATE table2 
SET table2.col1 = table1.col1, 
table2.col2 = table1.col2,
...
FROM table1, table2 
WHERE table1.memberid = table2.memberid

All other queries using NOT IN or NOT EXISTS are not recommended. NULLs show up because OP compares entire dataset with smaller subset, then of course there will be matching problem. This must be fixed by writing proper SQL with correct JOIN instead of dodging problem by using NOT IN. You might run into other problems by using NOT IN or NOT EXISTS in this case.

不建议使用不存在或不存在的所有其他查询。因为OP将整个数据集与更小的子集进行比较，所以出现了NULLs，当然会出现匹配问题。这必须通过使用NOT IN而不是使用正确的JOIN来编写正确的SQL来解决。在这种情况下，如果使用NOT IN或NOT exist，您可能会遇到其他问题。

My vote for the top one, which is conventional way of updating a table based on another table by joining in SQL Server. Like I said, you cannot use two tables in same UPDATE statement in SQL Server unless you join them first.

我选择了最上面的一个，这是通过加入SQL Server来更新基于另一个表的表的传统方式。如前所述，除非您先加入两个表，否则不能在SQL Server中使用同一个UPDATE语句中的两个表。

#13

it works with postgresql

它使用postgresql

UPDATE application
SET omts_received_date = (
    SELECT
        date_created
    FROM
        application_history
    WHERE
        application.id = application_history.application_id
    AND application_history.application_status_id = 8
);

#14

I thought this is a simple example might someone get it easier,

我想这是一个简单的例子，

        DECLARE @TB1 TABLE
        (
            No Int
            ,Name NVarchar(50)
        )

        DECLARE @TB2 TABLE
        (
            No Int
            ,Name NVarchar(50)
        )

        INSERT INTO @TB1 VALUES(1,'asdf');
        INSERT INTO @TB1 VALUES(2,'awerq');


        INSERT INTO @TB2 VALUES(1,';oiup');
        INSERT INTO @TB2 VALUES(2,'lkjhj');

        SELECT * FROM @TB1

        UPDATE @TB1 SET Name =S.Name
        FROM @TB1 T
        INNER JOIN @TB2 S
                ON S.No = T.No

        SELECT * FROM @TB1

#15

This will allow you to update a table based on the column value not being found in another table.

这将允许您基于在另一个表中没有找到的列值更新表。

    UPDATE table1 SET table1.column = 'some_new_val' WHERE table1.id IN (
            SELECT * 
            FROM (
                    SELECT table1.id
                    FROM  table1 
                    LEFT JOIN table2 ON ( table2.column = table1.column ) 
                    WHERE table1.column = 'some_expected_val'
                    AND table12.column IS NULL
            ) AS Xalias
    )

This will update a table based on the column value being found in both tables.

这将根据在两个表中找到的列值更新表。

    UPDATE table1 SET table1.column = 'some_new_val' WHERE table1.id IN (
            SELECT * 
            FROM (
                    SELECT table1.id
                    FROM  table1 
                    JOIN table2 ON ( table2.column = table1.column ) 
                    WHERE table1.column = 'some_expected_val'
            ) AS Xalias
    )

#16

try this :

试试这个:

UPDATE
    Table_A
SET
    Table_A.AccountNumber = Table_B.AccountNumber ,
FROM
    dbo.Sales_Import AS Table_A
    INNER JOIN dbo.RetrieveAccountNumber AS Table_B
        ON Table_A.LeadID = Table_B.LeadID 
WHERE
    Table_A.LeadID = Table_B.LeadID

#17

Use the following block of query to update Table1 with Table2 based on ID:

使用下面的查询块来更新Table1，表2基于ID:

UPDATE Sales_Import, RetrieveAccountNumber 
SET Sales_Import.AccountNumber = RetrieveAccountNumber.AccountNumber 
where Sales_Import.LeadID = RetrieveAccountNumber.LeadID;

This is the easiest way to tackle this problem.

这是解决这个问题的最简单的方法。

#18

I'd like to add one extra thing.

我想补充一点。

Don't update a value with the same value, it generates extra logging and unnecessary overhead. See example below - it will only perform the update on 2 records despite linking on 3.

不要更新具有相同值的值，它会生成额外的日志记录和不必要的开销。请看下面的例子——它只对2条记录执行更新，尽管链接是3条。

DROP TABLE #TMP1
DROP TABLE #TMP2
CREATE TABLE #TMP1(LeadID Int,AccountNumber NVarchar(50))
CREATE TABLE #TMP2(LeadID Int,AccountNumber NVarchar(50))

INSERT INTO #TMP1 VALUES
(147,'5807811235')
,(150,'5807811326')
,(185,'7006100100007267039');

INSERT INTO #TMP2 VALUES
(147,'7006100100007266957')
,(150,'7006100100007267039')
,(185,'7006100100007267039');

UPDATE A
SET A.AccountNumber = B.AccountNumber
FROM
    #TMP1 A 
        INNER JOIN #TMP2 B
        ON
        A.LeadID = B.LeadID
WHERE
    A.AccountNumber <> B.AccountNumber  --DON'T OVERWRITE A VALUE WITH THE SAME VALUE

SELECT * FROM #TMP1

#19

If above answers not working for you try this

如果上面的答案不适合你，试试这个

Update Sales_Import A left join RetrieveAccountNumber B on A.LeadID = B.LeadID
Set A.AccountNumber = B.AccountNumber
where A.LeadID = B.LeadID