Delete from table based on no match to another table 1

[trenttc]

The following was posted in another forum. It's to delete all Table1 records not found in Table2. How can this be done in ASA since it complains about the asterisk?

DELETE Table1.*
FROM Table1 LEFT JOIN Table2
ON Table1.YourField1 = Table2.YourField2
WHERE Table2.YourField2 Is Null;

 

[qyllr]

hello,

try this...

assuming of course that there is FK constraint between tableName1 and tableName2...

use databaseName
go

begin tran
go

delete tableName1
where columnName1 not in
(select columnName2
from tableName2)
go

commit tran/rollback tran
go

hth,
q.

 

[grega]

Can you not just say ...

DELETE Table1
FROM Table1, Table2
WHERE Table1.YourField1 = Table2.YourField2
AND Table2.YourField2 Is Null

Greg.

 

[trenttc]

qyllr's solution works. I was not aware you could code statements in this manner.

 

[mikewoodhouse]

...or

Delete TargetTable
From TargetTable target
Where Not Exists
( Select 1 From RafTable ref
Where ref.SomeKey = target.SomeKey
)

...just a quick thought.