SQL 删除与同一表中多列匹配的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1040267/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-01 02:33:02 来源:igfitidea点击:
Delete rows with matching multiple columns same table
提问by Tom H
Using SQL, I have 5 columns: ssn, lastname, firstname, RF and a flag field. I need to go through this table and where the 4 columns are equal to another row and the value of the flag field in that row is equal to 2050, then delete that 2050 record.
使用 SQL，我有 5 列：ssn、lastname、firstname、RF 和 flag field。我需要查看这个表，其中 4 列等于另一行并且该行中标志字段的值等于 2050，然后删除该 2050 记录。
回答by RedFilter
delete from MyTable m
where flag = 2050
and exists (
select 1 from MyTable where
MyTable.ssn = m.ssn
and MyTable.lastname=m.lastname
and MyTable.firstname=m.firstname
and MyTable.RF=m.RF
and MyTable.flag <> 2050
)
回答by Tom H
DELETE
T1
FROM
My_Table T1
INNER JOIN My_Table T2 ON
T2.ssn = T1.ssn AND
T2.last_name = T1.last_name AND
T2.first_name = T1.first_name AND
T2.RF_name = T1.RF_name AND
T2.flag <> T1.flag
WHERE
T1.flag = 2050
回答by Yaakov Ellis
delete from TableName as tn
where tn.flag = 2050 and
exists (select * from TableName as tn2 where tn.ssn = tn2.ssn
and tn.lastname = tn2.lastname and tn.firstname = tn2.firstname
and tn.rf = tn2.rf and tn2.flag <> 2050)
回答by suathd
delete from Table1
where flag = 2050
and exists (select *
from Table2
where ssn = Table1.ssn
and lastname = Table1.lastname
and firstname = Table1.firstname
and rf = Table1.rf
and flag <> 2050)
