UPDATE statement is not restricted as intended using WHERE clauses

Question

UPDATE statement is not restricted as intended using WHERE clauses

yeomanie

Old Hand

Points: 305
More actions
July 5, 2011 at 9:04 am

#253996

Why does an UPDATE statement not use each WHERE clause to restrict values, and end up updating a number of values which are irrelevant?
OK as an introduction I am relatively new to this site and have been working on and off for the last few years with SQL but not in a strictly DBA capacity, more support resource up to now.
I have a test database where I have run an UPDATE script which has several WHERE clauses and hasn’t updated the correct results. Basically:
UPDATE calendar_date SET is_holiday = '1' WHERE month_number = ‘6’ AND day_name = ‘Monday’ AND date_number <=7
The objective is to update Public Holidays to reflect public holidays in June, where the day is the first Monday in June, every year (my country). But the actual result of this script was to update all rows where the month was June and the date was less than 7. So criteria 3 became the most important and the requirement for the day to be a Monday was bypassed.
Yikes!
I changed this script to be the following:
UPDATE calendar_date SET is_holiday = '1' WHERE month_number = ‘6’ AND day_name = ‘Monday’ AND date_number !>7
So the date should not be greater than 7 (ie during the first week of June) and the other criteria remain the same. This script set the exact desired number of rows correctly.
So my question is, if the WHERE clauses are supposed to complement each other – as I understand it – and restrict the search criteria applicable – then why did the first script not work as intended?
PS this is a test database, relax 🙂 but I am concerned at what would have happened if this was production data.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

an update statement with a WHERE follows the WHERE restrictions, so if you are getting more rows affected than you expect, the best thing to do is look at the rows you'd be updating;

SELECT *

FROM calendar_date

WHERE month_number = '6'

AND day_name = 'Monday'

AND date_number <=7

does the above date return more days than you expect?(maybe due to <= ?)

my Calendar table is a little different,month_number = '6' throws me off a little since I'd expect that to be an integer, and i'd think doing dateadd stuff is easier as integers as well, so we'd probably want to see the DDL of your calendar table to be sure if your SQL is pseudocode or the real statement;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

yeomanie Old Hand Points: 305 More actions · Answer 2

Hey thanks for this.

I got this database as a sample - not sure where from - but it contains days and dates for a number of years in order to create stored procedures for creation of public holidays.

The column names - looking at the db carefully - are:

calendar_month (int, not null)

calendar_day (int, not null)

is_special_day (int, null)

day_name (varchar(10), not null)

So the script used to check the numbers returned is:

select COUNT (*) from date_calendar

where calendar_month = '6'

and calendar_day <= 7

and day_name = 'Monday'

--> this returns 31 lines.

Changing the script to use a different operator:

select COUNT (*) from date_calendar

where calendar_month = '6'

and calendar_day !>7

and day_name = 'Monday'

--> this also returns 31 lines.

However in the UPDATE script this returned a number more lines using the <= operator.

Does this give you enough to go on?

GSquared SSC Guru Points: 260824 More actions · Answer 3

Are you familiar with using the Ouput clause in Updates?

That's where I'd start. You can see the before-and-after of the rows affected, and work out what part of the Where clause is miswritten, or what part of the data is in an unexpected state.

Wrap it in a transaction and roll it back, and you can change the query in various ways and see exactly what it's doing.

To be more specific, I'd need to at least see the actual query you're executing, and the create script for the table it's being run on.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

yeomanie Old Hand Points: 305 More actions · Answer 4

Hi

Thanks for updates from both. I discovered that running the script again seemed to have the correct effect, so the expected updates were performed and not more than that.

I expect that this was a typo or an error in the original script and as I didn't keep a copy I was typing from memory.

I appreciate the feedback and hope to come back with more questions in the near future.

Thanks a lot! 😀