SQL Update with Select不会更新所有正确的列

Question

我需要用同一表中另一条记录的值更新表的一列。

我的表看起来像这样:

File Number       Suffix         Tax
--------------------------------------
11997            N            504
11997            V              0
23761            N            103
23761            C              0
23761            V              0

我需要用相应'N'记录的Tax值更新任何具有0 Tax值的记录。

换句话说，结果表应该是这样的:

File Number       Suffix         Tax
--------------------------------------
11997            N            504
11997            V            504
23761            N            103
23761            C            103
23761            V            103

这是我尝试过的SQL:

CREATE TABLE #NRecord
(
FileNum int, 
Suffix Varchar(3), 
Tax smallint
)
INSERT INTO #NRecord
SELECT FileNum, Suffix, Tax
FROM DatabaseTable
WHERE Suffix = 'N' AND Tax > 0
UPDATE d  
SET d.Tax = #NRecord.Tax
FROM DatabaseTable d
INNER JOIN #NRecord ON (d.FileNum = #NRecord.FileNum)
DROP TABLE #NRecord

但是它并没有像我希望的那样更新。

任何帮助都是非常感谢的。

Answer 1

您甚至不需要为此创建临时表，您可以使用join进行更新:

update T
set Tax=S.Tax
from MyTbl T
inner join
MyTbl S
on T.[File Number]=S.[File Number]
and T.Tax=0 and T.Suffix<>'N'
and S.Tax<>0 and S.Suffix='N'

如果数据库不是SqlServer，语法可能会有所不同。

或:

update MyTbl
set Tax=(select S.Tax 
from MyTbl S 
where MyTbl.[File Number]=S.[File Number]
and S.Tax<>0 and S.Suffix='N'
)
where Tax=0 and Suffix<>'N'

Answer 2

Select FileNum, Suffix, Tax
into #NRecord
from DatabaseTable
where Suffix='N' and Tax>0;

UPDATE  d  SET   d.Tax = #NRecord.Tax
FROM DatabaseTable d
INNER JOIN #NRecord
ON (d.FileNum = #NRecord.FileNum);

DBFiddle演示编辑:简单:

UPDATE databaseTable SET   Tax = #NRecord.Tax
FROM #NRecord
where (databaseTable.FileNum = #NRecord.FileNum);

DBFiddle演示