Updating a ‘Non-Existent’ Record
And by non-existent, I mean exists in one place, but not in another (yes, I realize this article has a misleading title). File this under “more fun with SQL.”
If you ever want to change a value of a record in one table because it does not have a matching record in another, you can use a simple EXISTS check in your WHERE clause. This works best when there’s a foreign-key relationship between the tables or there is data in both tables that can be used for an exact match.
In a recent case, I wanted to mark said record as inactive. To make it inactive, I simply needed to set an IsActive field to ‘false’.
Here’s a sample that very closely matches what was done:
SET [IsActive] = 0
WHERE NOT EXISTS (SELECT * FROM Table2 WHERE Table2.KeyField = Table1.KeyField)
See? Simple AND Fun!