我有3张桌子:
Table1
-------
ID
Field1
Field2
Table2
_______
ID
Name
Table2_OLD
____________
ID
Name
我需要将表2中的ID更新为来自Table2的ID,其中Table2.Name = Table2_OLD.Name
Table2和Table2_OLD中的ID是不同的,只有名称才能获得正确的ID:
SELECT Table2.ID
FROM Table2
INNER JOIN Table2_OLD ON Table2.Name=Table2_OLD.Name
我写了以下update语句:
UPDATE Table1 SET Table1.ID=(SELECT DISTINCT t2.ID
FROM Table2 t2
INNER JOIN Table2_OLD t3
ON t2.Name=t3.Name
AND t2.ID=Table1.ID)
但它给了我一个错误
无法将Table1.ID更新为null`
我试着用
UPDATE Table1 SET Table1.ID = (SELECT DISTINCT t2.ID
FROM Table2 t2
INNER JOIN Table2_OLD t3
ON t2.Name=t3.Name
AND t2.ID=Table1.ID
AND NOT t2.ID is null)
但得到了相同的错误。
有人能帮上忙吗?
发布于 2018-06-19 02:29:55
还没有测试过,但Oracle中的相关更新应该可以工作:
UPDATE table1 t1
SET id = (SELECT DISTINCT t2.id
FROM table2 t2
JOIN table2_old t3 ON
t2.name = t3.name
WHERE t3.id = t1.id
)
WHERE EXISTS (
SELECT 1
FROM table2 t2
WHERE t3.id = t1.id
)
发布于 2018-06-19 02:31:16
如果你想更新JOIN
,你可以试试这个。
UPDATE (
SELECT t2.ID t2ID,t1.ID t1ID
FROM Table1 t1
INNER JOIN Table2_OLD t2Old ON t1.ID= t2Old.ID
INNER JOIN Table2 t2 ON t2.Name=t2Old.Name
WHERE t2.ID is NOT null
)
SET t1ID = t2ID
https://stackoverflow.com/questions/50915376
复制相似问题