Click here to Skip to main content
13,863,479 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


11 bookmarked
Posted 19 Oct 2009
Licenced CPOL

Why SQL Updates Fail – Five Reasons

, 31 Dec 2013
Rate this:
Please Sign up or sign in to vote.
"The SQL command ran, there's no error message but nothing changed! What's going on? Do you think it's a virus? Maybe there's a bug in .NET!"

"The SQL command ran, there's no error message but nothing changed! What's going on? Do you think it's a virus? Maybe there's a bug in .NET!"

The junior programmer was almost in tears when he came to me for help. Proud and confident in his abilities and faced with failure on what should have been an easy task, the poor little fellow's puffed up ego collapsed like a house of cards. I patted him gently on the shoulder and said soothingly, "It's because you're an idiot."

OK, I didn't really say that.

Actually, having an SQL update statement not change any records and not produce an error is expected and desirable behavior:

If you run an update to add a late fee to all outstanding accounts over 2 weeks late and there are no late accounts….the update will succeed, no records will be modified and there will be no error message. This is as it should be.

When an update does not produce an error and no records are modified, it's because the WHERE clause did not locate any records to update. Sometimes this is OK, other times it is a mistake.

Here are three ways this can happen by mistake.

Case Sensitivity Misses

If the collating sequence of the database or column is case sensitive, the text in the column may not match the text in the WHERE clause: Z123 does NOT equal z123. In SQL Server, the default is case insensitive.

Space Padded Data

The field type in the database may space pad the data. In SQL Server, if a column type is char(6) or nchar(6) and 'ABC' is saved, what is actually put in the table is 'ABC ' (note the 3 spaces after ABC). A search for 'ABC' will fail because 'ABC' does not equal 'ABC '.

Dates with Extraneous Time Stamps

DateTime columns have two components (Date and Time…duh). If you are searching a DateTime column either the data or the search criteria may include a timestamp that causes the search to fail:

12-31-2000 12:23:45 AM does not equal 12-31-2000 12:00:00 AM

When storing dates, it is a best practice to truncate the time of DateTime fields so the times are zero (or 12:00:00 AM).

Here is a SQL statement to scrub existing data:

UPDATE MyTable SET MyDate = dateadd(dd, datediff(dd, 0, MyDate), 0) 

When searching for records, you can set the search criteria to include only the date by using the Date property of the DateTime structure:

DateTime SearchDate = DateTime.Now.Date; 

Note: SQL Server 2008 has new data types of just DATE and TIME.

Bonus: Floating Point Numbers

This is extremely rare but if you have floating point numbers in the data and expect to use an exact WHERE clause to retrieve and update data, good luck. As everyone knows who's taken Numerical Analysis 1001:

1.50000000000 does not equal 1.49999999999


If you are unsure of what is going to be updated in a statement, it is easy to replace the UPDATE with a SELECT and check what is actually being retrieved.

I hope this helps someone.


One more thing that can cause a WHERE clause to fail….

A field containing NULL will not be found if the WHERE clause is looking for an empty string (and vice versa).


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Steve Wellens
EndWell Software, Inc.
United States United States
I am an independent contractor/consultant working in the Twin Cities area in Minnesota. I work in .Net, Asp.Net, C#, C++, XML, SQL, Windows Forms, HTML, CSS, etc., etc., etc.

You may also be interested in...

Comments and Discussions

GeneralThanks Pin
SoMad30-Dec-13 18:12
protectorSoMad30-Dec-13 18:12 
GeneralGood Article Pin
Sameh Saeed Mohammad10-Dec-10 21:04
professionalSameh Saeed Mohammad10-Dec-10 21:04 
GeneralMy vote of 5 Pin
thatraja6-Oct-10 21:06
protectorthatraja6-Oct-10 21:06 
GeneralGood tip Pin
hermiod24-Oct-09 14:56
memberhermiod24-Oct-09 14:56 
GeneralRe: Good tip Pin
Steve Wellens24-Oct-09 16:01
memberSteve Wellens24-Oct-09 16:01 
GeneralYes, and wrap everything in a transaction. Pin
wtwhite27-Oct-09 15:34
memberwtwhite27-Oct-09 15:34 
GeneralRe: Yes, and wrap everything in a transaction. Pin
Steve Wellens27-Oct-09 16:25
memberSteve Wellens27-Oct-09 16:25 
GeneralRe: Yes, and wrap everything in a transaction. Pin
wtwhite27-Oct-09 16:41
memberwtwhite27-Oct-09 16:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web02 | 2.8.190214.1 | Last Updated 31 Dec 2013
Article Copyright 2009 by Steve Wellens
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid