如何删除使用SQL在两个独立表中匹配的行?
问题描述:
在使用SQL Server Management Studio进行SQL编码时,我可以从插入或删除时具有不同行数的表中删除。在我更新了表A之后,如果表B中的匹配项存在,我希望从表A中删除匹配的项目。这个想法最终将只包含表A中已更新的行。我有10列,但将使用4把事情简单化。如何删除使用SQL在两个独立表中匹配的行?
CREATE TRIGGER utr_Updates
ON tableA
AFTER UPDATE
AS BEGIN
SET NOCOUNT ON;
EXEC usp_updates
END
CREATE PROCEDURE usp_Updates
AS
BEGIN
IF EXISTS (SELECT * FROM tableA
WHERE col1 IN (SELECT DISTINCT col1 FROM tableB))
DELETE FROM tableA
WHERE col1 IN (SELECT DISTINCT col1 FROM tableB)
IF EXISTS (SELECT * FROM tableA
WHERE col2 IN (SELECT DISTINCT col2 FROM tableB))
DELETE FROM tableA
WHERE col2 IN (SELECT DISTINCT col2 FROM tableB)
IF EXISTS (SELECT * FROM tableA
WHERE col3 IN (SELECT DISTINCT col3 FROM tableB))
DELETE FROM tableA
WHERE col3 IN (SELECT DISTINCT col3 FROM tableB)
IF EXISTS (SELECT * FROM tableA
WHERE col4 IN (SELECT DISTINCT col4 FROM tableB))
DELETE FROM tableA
WHERE col4 IN (SELECT DISTINCT col4 FROM tableB)
END
START POINT
START
TABLE A TABLE B
col1 W, X, Y, Z col1 W, X, Y, Z
col2 1, 2, 3, 4 col2 1, 2, 3, 4
col3 A, 5, 6, D col3 A, 5, 6, D
col4 7, B, C, D col3 7, B, C, D
表A更新时间COL1和COL3
AFTER UPDATE
TABLE A TABLE B
col1 W, S, Y, Z col1 W, X, Y, Z
col2 1, 2, 3, 4 col2 1, 2, 3, 4
col3 A, 5, 1, D col3 A, 5, 6, D
col4 7, B, C, D col3 7, B, C, D
期望的结果
TABLE A
col1 W, S, Y, Z
col3 A, 5, 1, D
答
您可以使用NOT EXISTS
:
select a.*
from a
where not exists (select 1
from b
where b.col1 = a.col1 and b.col2 = a.col2 and b.col3 = a.col3 and b.col4 = a.col4
);
或者,在SQL Server中,你可以使用EXCEPT
:
select a.*
from a
except
select b.*
from b;