Structuring UPDATE and DELETE queries so that they're a little bit safer

Always practice safe querying!

Quite often I've seen someone who wants to update a table, or delete rows from a table write a SELECT query to work out what data they want then separately write an UPDATE or DELETE query to perform the actual task. In the best scenarios where I've seen that happen, the WHERE has been copied and pasted across, in the (nearly) worst it's been written again. In the very worst the second query that was having the UPDATE peformed against it was actually performed against a different table that happened to contain columns with the same names as those specified in the WHERE (thanks for that, Connor!).

There is a safer way to write queries when you're trying to determine what rows you want to affect, which is to swap this:

FROM	[dbo].[User]
WHERE	LockedOut = 0
AND	ManagerUserId = 3

DELETE	[dbo].[User]
WHERE	LockedOut = 0
AND	ManagerUserId = 3

For this:

FROM	[dbo].[User] U
WHERE	U.LockedOut = 0
AND	U.ManagerUserId = 3

By structuring the query in this way and then swapping the SELECT * for the DELETE or UPDATE the likelihood of making a mistake is much reduced as the same query is effectively being used to determine what data to change and to carry out the change.

Comment out the SELECT, un-comment the DELETE, press F5 and it's job done, safely.

About Rob

I've been interested in computing since the day my Dad purchased his first business PC (an Amstrad PC 1640 for anyone interested) which introduced me to MS-DOS batch programming and BASIC.

My skillset has matured somewhat since then, which you'll probably see from the posts here. You can read a bit more about me on the about page of the site, or check out some of the other posts on my areas of interest.

No Comments

Add a Comment