Link to home
Start Free TrialLog in
Avatar of gsszuber
gsszuber

asked on

Update Query with multiple WHERE conditions

I think this should be pretty easy for someone to answer.  I'm trying to clean up our data a bit and I have an update query that doesn't seem to be updating all the rows I thought it would.  The second I add multiple INs to the WHERE clause, it updates far fewer rows than I would expect.  When I refresh a report based on this data, there are still records with incorrect data I was trying to update.
UPDATE CustLine
SET Style = 'CARPET CLEANING - IN-PLANT', Pr_Code = '92'
WHERE Pr_Code IN ('91', '92') AND JobCostDate <= '20020101' AND Style IN 
('BROT-IN CLEANING', 'IN-PLANT CLEANING G','IN-PLANT CLEANING P',
'IN-PLANT CLEANING S', 'PICK-UP CLEANING', '1N-PLANT CLEANING G',
'.N-PLANT CLEANING S', '1I-PLANT CLEANING S', '1N-PLANT CLEANING G',
'CARPET CLEANED', 'CARPET CLEANING', 'CLEAN');

Open in new window

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

what is the data type of Style column, please?
The question is why do you think it should have updated more rows than it did?

Can you give an example of such a row?
could you please show the SQL of the report that is showing the rows that have not changed?
Avatar of gsszuber
gsszuber

ASKER

Rather than showing you the report, which covers everything I'm trying to clean up (which just keeps growing), I thought it would be simpler to just roll back the db to a recent backup of the live db and run some quick select queries before and after the update.
Before.png
UpdateQuery.png
After.png
ASKER CERTIFIED SOLUTION
Avatar of Zberteoc
Zberteoc
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I would have a hard time knowing more unless I see more about the data types that you are using, but here are a couple suggestions.

1. You are not using a FROM statement in your update, this tells sql the table source to use when using a join or a where clause.
2. are you using a varchar for your style? if not you might want to trim it I don't know how sql handles spaces at the beginning or end of a string

and example would be
UPDATE CustLine
SET Style = 'CARPET CLEANING - IN-PLANT', Pr_Code = '92'
WHERE Pr_Code IN ('91', '92') AND JobCostDate <= '20020101' AND TRIM(Style) IN 
('BROT-IN CLEANING', 'IN-PLANT CLEANING G','IN-PLANT CLEANING P',
'IN-PLANT CLEANING S', 'PICK-UP CLEANING', '1N-PLANT CLEANING G',
'.N-PLANT CLEANING S', '1I-PLANT CLEANING S', '1N-PLANT CLEANING G',
'CARPET CLEANED', 'CARPET CLEANING', 'CLEAN');

Open in new window

example with from would look like this
UPDATE CustLine
SET Style = 'CARPET CLEANING - IN-PLANT', Pr_Code = '92'
FROM CustLine
WHERE Pr_Code IN ('91', '92') AND JobCostDate <= '20020101' AND TRIM(Style) IN 
('BROT-IN CLEANING', 'IN-PLANT CLEANING G','IN-PLANT CLEANING P',
'IN-PLANT CLEANING S', 'PICK-UP CLEANING', '1N-PLANT CLEANING G',
'.N-PLANT CLEANING S', '1I-PLANT CLEANING S', '1N-PLANT CLEANING G',
'CARPET CLEANED', 'CARPET CLEANING', 'CLEAN');

Open in new window

CustLine.Style is a varchar(80).  I'm going back to my office momentarily, but probably won't have more time to put into this until tonight.  I'm going to roll back the db again and run my query again, this time saving the results as a .pdf so you can observe exactly what changed.  Thanks all for you help!  I'm raising the points because this seems to be more complicated than I had originally thought it would be.
I'm rather embarrassed right now.  :-| The query was doing exactly what I was telling it to do.  I had JobCostDate <= '20020101' when it should have been JobCostDate >= '20020101'.  Since my select query and reports were written correctly and my update query was not, naturally it didn't produce the results I was expecting.