SQL Server delete query involving two tables

Question

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.

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 .

Does this involve aliases? It is confusing..

Answer 1

Try this if you are using SQL Server 2005 or newer:

DELETE FROM TableA
WHERE a < 100 AND 
a NOT IN (SELECT B FROM TableB)

For SQL Server 2000 this should work:

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