When a SQL UPDATE Statement DELETES Rows

Published on: 2019-11-26

Watch this week’s episode on YouTube.

At first I wasn’t going to write this post. I thought it would be too simple.

Then I watched two experienced SQL developers go through similar scenarios last week.

Sometimes the simple things are worth revisiting, so…

A SQL Server UPDATE Bug

I received a message from someone stating that when they update a row in their table, the row gets deleted.

Thinking that was strange, I asked the user if they could reproduce the issue. And they did. And after running the update statement, the row disappeared.

“WHAT THE…?”

Reproducing the Issue

So here’s the scenario: we had an SSIS configuration table that looks something like this:

DROP TABLE IF EXISTS ##Configuration;
CREATE TABLE ##Configuration	
(
	ConfigurationFilter nvarchar(255) PRIMARY KEY,
	ConfiguredValue nvarchar(255),
	ConfiguredValueType nvarchar(20)
	-- some other fields
);

If you use SSIS, you might be familiar with this setup. In the table we had some innocuous looking rows:

INSERT INTO ##Configuration VALUES ('AdventureWorks_ETL_Bypass','1','int');
INSERT INTO ##Configuration VALUES ('WideWorldImporters_ETL_Bypass','0','int');
INSERT INTO ##Configuration VALUES ('Northwind_ETL_Bypass','1','int');

Querying a single ConfigurationFilter value returns a single row:

SELECT * FROM ##Configuration WHERE ConfigurationFilter = 'AdventureWorks_ETL_Bypass'
1 record found

Let’s say we now want to update the 1 value to a 0::

UPDATE ##Configuration SET ConfigurationFilter = '0' 
WHERE ConfigurationFilter = 'AdventureWorks_ETL_Bypass'

Then, let’s check to see if our change went through:

SELECT * FROM ##Configuration WHERE ConfigurationFilter = 'AdventureWorks_ETL_Bypass'
no rows!

“WHAT THE …?”

Following the Rules

Do you see the problem?

Of course you do. But in the excitement of the moment, I didn’t see the issue. I thought there was some SQL Server feature taking over that I didn’t understand. Or possibly a bug in how UPDATE works in certain scenarios. How could an UPDATE possibly DELETE a row of data?

Look at the above UPDATE statement again. Our WHERE clause is filtering on the ConfigurationFilter field, which in this case is our table’s primary key; it will only ever return one unique row.

That is until we change the value of that row’s primary key: the SET clause is also updating ConfigurationFilter. This is the mistake. Since ConfigurationFilter has a different value after the update, our original query makes it appear that the row was deleted – when in reality it is now considered a different row based on the way we defined our primary key:

changed it to a 0...whoops

Since there a lot of “Config…” names in this table, the field used in the SET statement should have been ConfiguredValue instead of ConfigurationName. Simple case of updating the wrong field.

Lesson Learned

SQL Server has been thoroughly vetted by running on millions(?) of systems. Bugs do exist, but the chances of you discovering a bug, let alone one that affects such a basic feature such as UPDATE, is very unlikely at this point.

The lesson here is that if you do think you find an issue, go back and check your query: it’s more likely there was an error with the connection between chair and keyboard rather than with the tool itself.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

3 thoughts on “When a SQL UPDATE Statement DELETES Rows”

  1. Good one Bert! You definitely got me going “Whoa…what’s that?”
    BTW: Just had realized from another example that learning in your lunch time sometimes doesn’t work out well because you are not able to pay attention to every detail going on…be smarter than me and reserve your time for eating :-).

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.