So I have tables A and B in SQL Server, and columns a and b respectively. I want to do the following in pseudo-query command, but I can't seem to figure it out.
所以我在SQL Server中有表A和B，分别在a和b列。我想在伪查询命令中执行以下操作，但我似乎无法弄明白。
I want to
我要
DELETE FROM A
WHERE a < 100 "and only if these selected (for deletion) values don't exist in column b in table B"
The reason is that I'm trying to delete some data from table A, but it is giving me an error saying that there is a constraint between values in A.a and B.b .
原因是我试图从表A中删除一些数据，但是它给出了一个错误，说明A.a和B.b中的值之间存在约束。
Does this involve aliases? It is confusing..
这是否涉及别名？令人困惑的是......
1 个解决方案
#1
11
Try this if you are using SQL Server 2005 or newer:
如果您使用的是SQL Server 2005或更高版本，请尝试此操作：
DELETE FROM TableA
WHERE a < 100 AND
a NOT IN (SELECT B FROM TableB)
For SQL Server 2000 this should work:
对于SQL Server 2000，这应该工作：
DELETE ta
FROM TableA as ta
LEFT JOIN TableB as tb
ON ta.a = tb.b
WHERE ta.a < 100 AND tb.b IS NULL
#1
11
Try this if you are using SQL Server 2005 or newer:
如果您使用的是SQL Server 2005或更高版本，请尝试此操作：
DELETE FROM TableA
WHERE a < 100 AND
a NOT IN (SELECT B FROM TableB)
For SQL Server 2000 this should work:
对于SQL Server 2000，这应该工作：
DELETE ta
FROM TableA as ta
LEFT JOIN TableB as tb
ON ta.a = tb.b
WHERE ta.a < 100 AND tb.b IS NULL