SQL Server Two-Table Uninstall Request

Question

So, I have tables A and B in SQL Server and columns a and b respectively. I want to do the following in a pseudo query command, but I cannot 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 am trying to delete some data from table A, but this gives me an error saying that there is a restriction between the values ​​in Aa and Bb

Is this related to aliases? This is confusing.

Answer 1

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

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