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:

UPDATE Table1
SET [IsActive] = 0
WHERE NOT EXISTS (SELECT * FROM Table2 WHERE Table2.KeyField = Table1.KeyField)

See? Simple AND Fun!

Advertisements

About Ed Jones

Ed is a Connected Systems and .NET Specialist for RBA in the Twin Cities. Contact Ed

What do you think?

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

%d bloggers like this: