当前位置： 文江博客话题详情

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

发布于 2024-07-07 21:12:56 字数 1209 浏览 7 评论 0原文

我有一个包含帐号和卡号的数据库。 我将它们与一个文件相匹配，以将任何卡号更新为帐号，以便我只使用帐号。

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

这是 Sales_Import 表，其中的 帐号 字段需要更新：

LeadIDAccountNumber
1475807811235
1505807811326
1857006100100007267039

这是 RetrieveAccountNumber表，我需要从其中更新：

LeadIDAccountNumber
1477006100100007266957
1507006100100007267039

我尝试了以下操作，但到目前为止没有运气：

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

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

原文

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 the Account Number.

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

LeadIDAccountNumber
1475807811235
1505807811326
1857006100100007267039

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

LeadIDAccountNumber
1477006100100007266957
1507006100100007267039

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 get replaced by NULL

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（29）

随梦而飞# 2024-07-14 21:12:57

对于 SQL Server 2008 + 使用 MERGE 而不是专有的 UPDATE ... FROM 语法具有一定的吸引力。

除了是标准 SQL 因而更加可移植之外，如果源端存在多个连接行（因此在更新中可能使用多个不同的值），它也会引发错误，而不是让最终结果不确定。

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

不幸的是，使用哪个的选择可能并不纯粹取决于首选的风格。 SQL Server 中 MERGE 的实现受到各种错误的影响。 Aaron Bertrand 编制了此处报告的列表 。

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

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.

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.

回复 原文
自我难过 2024-07-14 21:12:57

未来开发人员的通用答案。

SQL Server

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

Oracle（和 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;

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;
回复 原文
空宴 2024-07-14 21:12:57

对于 PostgreSQL：

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

For PostgreSQL:

UPDATE Sales_Import SI
SET AccountNumber = RAN.AccountNumber
FROM RetrieveAccountNumber RAN
WHERE RAN.LeadID = SI.LeadID;
回复 原文
太傻旳人生 2024-07-14 21:12:57

看来你使用的是MSSQL，那么，如果我没记错的话，它是这样完成的：

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

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

UPDATE [Sales_Lead].[dbo].[Sales_Import] SET [AccountNumber] = 
RetrieveAccountNumber.AccountNumber 
FROM RetrieveAccountNumber 
WHERE [Sales_Lead].[dbo].[Sales_Import].LeadID = RetrieveAccountNumber.LeadID
回复 原文
夏尔 2024-07-14 21:12:57

对于在 barfoo 行，我也遇到了同样的问题，将 foo.new 设置为 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)

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:

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)
回复 原文
∝单色的世界 2024-07-14 21:12:57

以下是在 SQL Server 中对我有用的内容：

UPDATE [AspNetUsers] SET

[AspNetUsers].[OrganizationId] = [UserProfile].[OrganizationId],
[AspNetUsers].[Name] = [UserProfile].[Name]

FROM [AspNetUsers], [UserProfile]
WHERE [AspNetUsers].[Id] = [UserProfile].[Id];

Here's what worked for me in SQL Server:

UPDATE [AspNetUsers] SET

[AspNetUsers].[OrganizationId] = [UserProfile].[OrganizationId],
[AspNetUsers].[Name] = [UserProfile].[Name]

FROM [AspNetUsers], [UserProfile]
WHERE [AspNetUsers].[Id] = [UserProfile].[Id];
回复 原文
笑叹一世浮沉 2024-07-14 21:12:57

对于运行良好的 MySql：

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

For MySql that works fine:

UPDATE
    Sales_Import SI,RetrieveAccountNumber RAN
SET
    SI.AccountNumber = RAN.AccountNumber
WHERE
    SI.LeadID = RAN.LeadID
回复 原文
谁的新欢旧爱 2024-07-14 21:12:57

感谢您的回复。 我找到了一个解决方案。

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)

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)
回复 原文
梦年海沫深 2024-07-14 21:12:57

如果表位于不同的数据库中。 (MSSQL)

update database1..Ciudad
set CiudadDistrito=c2.CiudadDistrito

FROM database1..Ciudad c1
 inner join 
  database2..Ciudad c2 on c2.CiudadID=c1.CiudadID

In case the tables are in a different databases. (MSSQL)

update database1..Ciudad
set CiudadDistrito=c2.CiudadDistrito

FROM database1..Ciudad c1
 inner join 
  database2..Ciudad c2 on c2.CiudadID=c1.CiudadID
回复 原文
花开柳相依 2024-07-14 21:12:57

使用以下查询块根据 ID 将 Table1 更新为 Table2：

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

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

Use the following block of query to update Table1 with Table2 based on 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.

回复 原文
安人多梦 2024-07-14 21:12:57

微软 SQL

UPDATE  c4 SET Price=cp.Price*p.FactorRate FROM TableNamea_A c4
inner join TableNamea_B p on c4.Calcid=p.calcid 
inner join TableNamea_A cp on c4.Calcid=cp.calcid 
WHERE c4..Name='MyName';

甲骨文 11g

        MERGE INTO  TableNamea_A u 
        using
        (
                SELECT c4.TableName_A_ID,(cp.Price*p.FactorRate) as CalcTot 
                FROM TableNamea_A c4
                inner join TableNamea_B p on c4.Calcid=p.calcid 
                inner join TableNamea_A cp on c4.Calcid=cp.calcid 
                WHERE p.Name='MyName' 
        )  rt
        on (u.TableNamea_A_ID=rt.TableNamea_B_ID)
        WHEN MATCHED THEN
        Update set Price=CalcTot  ;

MS Sql

UPDATE  c4 SET Price=cp.Price*p.FactorRate FROM TableNamea_A c4
inner join TableNamea_B p on c4.Calcid=p.calcid 
inner join TableNamea_A cp on c4.Calcid=cp.calcid 
WHERE c4..Name='MyName';

Oracle 11g

        MERGE INTO  TableNamea_A u 
        using
        (
                SELECT c4.TableName_A_ID,(cp.Price*p.FactorRate) as CalcTot 
                FROM TableNamea_A c4
                inner join TableNamea_B p on c4.Calcid=p.calcid 
                inner join TableNamea_A cp on c4.Calcid=cp.calcid 
                WHERE p.Name='MyName' 
        )  rt
        on (u.TableNamea_A_ID=rt.TableNamea_B_ID)
        WHEN MATCHED THEN
        Update set Price=CalcTot  ;
回复 原文
旧瑾黎汐 2024-07-14 21:12:57

根据 id 匹配从一张表更新到另一张表

UPDATE 
     TABLE1 t1, 
     TABLE2 t2
SET 
     t1.column_name = t2.column_name 
WHERE
     t1.id = t2.id;

update from one table to another table on id matched

UPDATE 
     TABLE1 t1, 
     TABLE2 t2
SET 
     t1.column_name = t2.column_name 
WHERE
     t1.id = t2.id;
回复 原文
左耳近心 2024-07-14 21:12:57

有人建议的下面的 SQL 在 SQL Server 中不起作用。 这种语法让我想起了我以前的学校课程：

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

不推荐使用 NOT IN 或 NOT EXISTS 的所有其他查询。 出现 NULL 是因为 OP 将整个数据集与较小的子集进行比较，那么当然会出现匹配问题。 必须通过使用正确的 JOIN 编写正确的 SQL 来解决此问题，而不是使用 NOT IN 来避免问题。 在这种情况下，使用 NOT IN 或 NOT EXISTS 可能会遇到其他问题。

我投票给最上面的一个，这是通过加入 SQL Server 来基于另一个表更新一个表的传统方法。 正如我所说，您不能在 SQL Server 中的同一个 UPDATE 语句中使用两个表，除非您先将它们连接起来。

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

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.

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.

回复 原文
酒与心事 2024-07-14 21:12:57

这是 Mysql 和 Maria DB 中最简单、最好的方法。

UPDATE table2, table1 SET table2.by_department = table1.department WHERE table1.id = table2.by_id

注意：如果您根据您的 Mysql/Maria DB 版本遇到以下错误“错误代码：1175。您正在使用安全更新模式，并且尝试在没有安全更新的情况下更新表”。 WHERE 使用 KEY 列要禁用安全模式，请在“首选项”中切换该选项，

然后运行如下代码

SET SQL_SAFE_UPDATES=0;
UPDATE table2, table1 SET table2.by_department = table1.department WHERE table1.id = table2.by_id

This is the easiest and best have seen for Mysql and Maria DB

UPDATE table2, table1 SET table2.by_department = table1.department WHERE table1.id = table2.by_id

Note: If you encounter the following error based on your Mysql/Maria DB version "Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences"

Then run the code like this

SET SQL_SAFE_UPDATES=0;
UPDATE table2, table1 SET table2.by_department = table1.department WHERE table1.id = table2.by_id
回复 原文
桃扇骨 2024-07-14 21:12:57

总结其他答案，仅当“匹配存在”时，如何使用另一个表中的数据更新目标表有 4 种变体

查询和子查询：

update si
set    si.AccountNumber = (
    select ran.AccountNumber 
    from   RetrieveAccountNumber ran
    where  si.LeadID = ran.LeadID
)
from Sales_Import si
where exists (select * from RetrieveAccountNumber ran where ran.LeadID = si.LeadID)

内连接：

update si
set si.AccountNumber = ran.AccountNumber
from Sales_Import si inner join RetrieveAccountNumber ran on si.LeadID = ran.LeadID

交叉连接：

update si
set si.AccountNumber = ran.AccountNumber
from Sales_Import si, RetrieveAccountNumber ran
where si.LeadID = ran.LeadID

合并：

merge into Sales_Import si
using RetrieveAccountNumber ran on si.LeadID = ran.LeadID 
when matched then update set si.accountnumber = ran.accountnumber;

所有变体都更不那么琐碎且易于理解，我个人更喜欢“内部连接”选项。 但它们中的任何一个都可以使用，开发人员必须根据他/她的需求选择“更好的选项”

从性能角度来看，带有 join-s 的变体更可取：
输入图像描述这里

Summarizing the other answers, there're 4 variants of how to update target table using data from another table only when "match exists"

Query and sub-query:

update si
set    si.AccountNumber = (
    select ran.AccountNumber 
    from   RetrieveAccountNumber ran
    where  si.LeadID = ran.LeadID
)
from Sales_Import si
where exists (select * from RetrieveAccountNumber ran where ran.LeadID = si.LeadID)

Inner join:

update si
set si.AccountNumber = ran.AccountNumber
from Sales_Import si inner join RetrieveAccountNumber ran on si.LeadID = ran.LeadID

Cross join:

update si
set si.AccountNumber = ran.AccountNumber
from Sales_Import si, RetrieveAccountNumber ran
where si.LeadID = ran.LeadID

Merge:

merge into Sales_Import si
using RetrieveAccountNumber ran on si.LeadID = ran.LeadID 
when matched then update set si.accountnumber = ran.accountnumber;

All variants are more-less trivial and understandable, personally I prefer "inner join" option. But any of them could be used and developer has to select "better option" according to his/her needs

From performance perspective variants with join-s are more preferable:
enter image description here

回复 原文
我纯我任性 2024-07-14 21:12:57

它与 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
);

it works with 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
);
回复 原文
别靠近我心 2024-07-14 21:12:57

在同一个表中更新：

  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

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
回复 原文
梦屿孤独相伴 2024-07-14 21:12:57

我认为这是一个简单的例子，可能有人会更容易理解，

        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

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
回复 原文
虫児飞 2024-07-14 21:12:57

试试这个：

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

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
回复 原文
怀念你的温柔 2024-07-14 21:12:57

MYSQL（这是我基于主键 id 等效性恢复所有特定列 reasonId 值的首选方法）

UPDATE `site` AS destination  
INNER JOIN `site_copy` AS backupOnTuesday 
      ON backupOnTuesday.`id` = destination.`id`
SET destdestination.`reasonId` = backupOnTuesday.`reasonId`

MYSQL (This is my preferred way for restoring all specific column reasonId values, based on primary key id equivalence)

UPDATE `site` AS destination  
INNER JOIN `site_copy` AS backupOnTuesday 
      ON backupOnTuesday.`id` = destination.`id`
SET destdestination.`reasonId` = backupOnTuesday.`reasonId`
回复 原文
梦冥 2024-07-14 21:12:57

这将允许您根据在另一个表中找不到的列值来更新表。

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
)

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

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
)

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
)
回复 原文
澜川若宁 2024-07-14 21:12:57

甲骨文11g

merge into Sales_Import
using RetrieveAccountNumber
on (Sales_Import.LeadId = RetrieveAccountNumber.LeadId)
when matched then update set Sales_Import.AccountNumber = RetrieveAccountNumber.AccountNumber;

Oracle 11g

merge into Sales_Import
using RetrieveAccountNumber
on (Sales_Import.LeadId = RetrieveAccountNumber.LeadId)
when matched then update set Sales_Import.AccountNumber = RetrieveAccountNumber.AccountNumber;
回复 原文
东走西顾 2024-07-14 21:12:57

对于 Oracle SQL 尝试使用别名

UPDATE Sales_Lead.dbo.Sales_Import SI 
SET SI.AccountNumber = (SELECT RAN.AccountNumber FROM RetrieveAccountNumber RAN WHERE RAN.LeadID = SI.LeadID);

For Oracle SQL try using alias

UPDATE Sales_Lead.dbo.Sales_Import SI 
SET SI.AccountNumber = (SELECT RAN.AccountNumber FROM RetrieveAccountNumber RAN WHERE RAN.LeadID = SI.LeadID);
回复 原文
眼眸里的那抹悲凉 2024-07-14 21:12:57

我想补充一件事。

不要使用相同的值更新值，它会生成额外的日志记录和不必要的开销。
请参见下面的示例 - 尽管链接了 3 条记录，但它只会对 2 条记录执行更新。

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

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.

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
回复 原文
逆流 2024-07-14 21:12:57

ORACLE

使用

UPDATE suppliers
SET supplier_name = (SELECT customers.customer_name
                     FROM customers
                     WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT customers.customer_name
              FROM customers
              WHERE customers.customer_id = suppliers.supplier_id);

ORACLE

use

UPDATE suppliers
SET supplier_name = (SELECT customers.customer_name
                     FROM customers
                     WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT customers.customer_name
              FROM customers
              WHERE customers.customer_id = suppliers.supplier_id);
回复 原文
め七分饶幸 2024-07-14 21:12:57

从 table2 dpu 更新 table1 dpm set col1 = dpu.col1 其中 dpm.parameter_master_id = dpu.parameter_master_id;

update table1 dpm set col1 = dpu.col1 from table2 dpu where dpm.parameter_master_id = dpu.parameter_master_id;

回复 原文
自由范儿 2024-07-14 21:12:57

如果以上答案不适合您，请尝试此操作

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

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
回复 原文
相思故 2024-07-14 21:12:56

我相信带有 JOIN 的 UPDATE FROM 会有所帮助：

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 和 MariaDB

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

I believe an UPDATE FROM with a JOIN will help:

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;
回复 原文
深海少女心 2024-07-14 21:12:56

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

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

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

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.

回复 原文
~没有更多了~

关于作者

浅忆

暂无简介

0 文章
0 评论
22 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

linfzu01

文章 0 评论 0

§对你不离不弃

文章 0 评论 0

可遇━不可求

文章 0 评论 0

枕梦

文章 0 评论 0

qq_3LFa8Q

文章 0 评论 0

JP

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文