Deleting Rows from one table based on another table

In this example, I needed to delete the rows with null values in the ##EncList table where the CaseNo matched the CaseNo in the ##EncListWithProcStartEnd table.

DELETE EL
FROM ##EncList EL
INNER JOIN ##EncListWithProcStartEnd
ON ##EncList.CaseNo = ##EncListWithProcStartEnd.CaseNo
WHERE ##EncList.Start IS NULL
AND ##EncList.End IS NULL

I made the nickname for the ##EncList in the “DELETE” phrase, because as pointed out on StackOverflow, if one accidentally had “DELETE ##EncList” highlighted while running this query, you would delete your whole table.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s