我有一个包含 account numbers
和 card numbers
的数据库。我将这些与文件相匹配,以update
将任何卡号与帐号匹配,以便我只使用帐号。
我创建了一个将表链接到帐户/卡数据库以返回 Table ID
和相关帐号的视图,现在我需要更新 ID 与帐号匹配的那些记录。
这是 Sales_Import
表,其中 account number
字段需要更新:
LeadID 帐号 147 5807811235 150 5807811326 185 7006100100007267039
这是 RetrieveAccountNumber
表,我需要从中更新:
LeadID 帐号 147 7006100100007266957 150 7006100100007267039
我尝试了以下方法,但到目前为止没有运气:
UPDATE [Sales_Lead].[dbo].[Sales_Import]
SET [AccountNumber] = (SELECT RetrieveAccountNumber.AccountNumber
FROM RetrieveAccountNumber
WHERE [Sales_Lead].[dbo].[Sales_Import]. LeadID =
RetrieveAccountNumber.LeadID)
它将卡号更新为帐号,但帐号被替换为 NULL
我相信带有 JOIN
的 UPDATE FROM
会有所帮助:
微软 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;
将内容从一个表复制到另一个表的简单方法如下:
UPDATE table2
SET table2.col1 = table1.col1,
table2.col2 = table1.col2,
...
FROM table1, table2
WHERE table1.memberid = table2.memberid
您还可以添加条件以获取复制的特定数据。
对于 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 编制了一份 the reported ones here 清单。
未来开发人员的通用答案。
SQL 服务器
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;
update t1...
而不是 update Table1...
)
对于 PostgreSQL:
UPDATE Sales_Import SI
SET AccountNumber = RAN.AccountNumber
FROM RetrieveAccountNumber RAN
WHERE RAN.LeadID = SI.LeadID;
似乎您正在使用MSSQL,那么,如果我没记错的话,它是这样完成的:
UPDATE [Sales_Lead].[dbo].[Sales_Import] SET [AccountNumber] =
RetrieveAccountNumber.AccountNumber
FROM RetrieveAccountNumber
WHERE [Sales_Lead].[dbo].[Sales_Import].LeadID = RetrieveAccountNumber.LeadID
对于在 bar
中没有匹配键的 foo
行,将 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)
以下是在 SQL Server 中对我有用的方法:
UPDATE [AspNetUsers] SET
[AspNetUsers].[OrganizationId] = [UserProfile].[OrganizationId],
[AspNetUsers].[Name] = [UserProfile].[Name]
FROM [AspNetUsers], [UserProfile]
WHERE [AspNetUsers].[Id] = [UserProfile].[Id];
对于运行良好的 MySql:
UPDATE
Sales_Import SI,RetrieveAccountNumber RAN
SET
SI.AccountNumber = RAN.AccountNumber
WHERE
SI.LeadID = RAN.LeadID
感谢您的回复。我找到了解决方案。
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)
如果表在不同的数据库中。 (MSSQL)
update database1..Ciudad
set CiudadDistrito=c2.CiudadDistrito
FROM database1..Ciudad c1
inner join
database2..Ciudad c2 on c2.CiudadID=c1.CiudadID
使用以下查询块根据 ID 将 Table1 更新为 Table2:
UPDATE Sales_Import, RetrieveAccountNumber
SET Sales_Import.AccountNumber = RetrieveAccountNumber.AccountNumber
where Sales_Import.LeadID = RetrieveAccountNumber.LeadID;
这是解决此问题的最简单方法。
微软 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 ;
有人建议的以下 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
语句中使用两个表,除非你先加入它们。
在 id 匹配时从一个表更新到另一个表
UPDATE
TABLE1 t1,
TABLE2 t2
SET
t1.column_name = t2.column_name
WHERE
t1.id = t2.id;
这是 Mysql 和 Maria DB 所见过的最简单和最好的
UPDATE table2, table1 SET table2.by_department = table1.department WHERE table1.id = table2.by_id
注意:如果您遇到以下基于您的 Mysql/Maria DB 版本的错误“错误代码:1175。您正在使用安全更新模式,并且您尝试更新没有使用 KEY 列的 WHERE 的表要禁用安全模式,请切换首选项中的选项”
然后像这样运行代码
SET SQL_SAFE_UPDATES=0;
UPDATE table2, table1 SET table2.by_department = table1.department WHERE table1.id = table2.by_id
在同一个表中更新:
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
它适用于 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
);
我认为这是一个简单的例子可能有人会更容易,
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
尝试这个 :
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
MYSQL(这是我根据主键 id
等效性恢复所有特定列 reasonId
值的首选方式)
UPDATE `site` AS destination
INNER JOIN `site_copy` AS backupOnTuesday
ON backupOnTuesday.`id` = destination.`id`
SET destdestination.`reasonId` = backupOnTuesday.`reasonId`
甲骨文 11g
merge into Sales_Import
using RetrieveAccountNumber
on (Sales_Import.LeadId = RetrieveAccountNumber.LeadId)
when matched then update set Sales_Import.AccountNumber = RetrieveAccountNumber.AccountNumber;
对于 Oracle SQL,请尝试使用别名
UPDATE Sales_Lead.dbo.Sales_Import SI
SET SI.AccountNumber = (SELECT RAN.AccountNumber FROM RetrieveAccountNumber RAN WHERE RAN.LeadID = SI.LeadID);
我想补充一件额外的事情。
不要使用相同的值更新值,它会产生额外的日志记录和不必要的开销。请参见下面的示例 - 尽管链接到 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
甲骨文
利用
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);
这将允许您根据在另一个表中未找到的列值来更新表。
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
)
如果上述答案对您不起作用,请尝试此
Update Sales_Import A left join RetrieveAccountNumber B on A.LeadID = B.LeadID
Set A.AccountNumber = B.AccountNumber
where A.LeadID = B.LeadID
不定期副业成功案例分享
SI.AccountNumber
更改为AccountNumber
否则它将失败。UPDATE Sales_Import, RetrieveAccountNumber SET Sales_Import.AccountNumber = RetrieveAccountNumber.AccountNumber where Sales_Import.LeadID = RetrieveAccountNumber.LeadID;
。稍微偏离主题,但可能会有所帮助