Solved

Update Query with multiple WHERE conditions

Posted on 2008-09-29
9
950 Views
Last Modified: 2012-06-22
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

0
Comment
Question by:gsszuber
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22600840
what is the data type of Style column, please?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22601278
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?
0
 
LVL 11

Expert Comment

by:Goodangel Matope
ID: 22601651
could you please show the SQL of the report that is showing the rows that have not changed?
0
 

Author Comment

by:gsszuber
ID: 22601969
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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 26

Accepted Solution

by:
Zberteoc earned 250 total points
ID: 22605040
Your Before and After images don't show the CARPET CLEANING - IN-PLANT style in order to tell if it was changed.

On the other hand there's nothing wrong with the update statement and as we can see it was executed. The fact that you expected different results doesn't really matter, it was executed the way it was written. If you want different results maybe you should change the update statement accordingly.
0
 
LVL 5

Expert Comment

by:brady1408
ID: 22607193
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

0
 
LVL 5

Expert Comment

by:brady1408
ID: 22607203
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

0
 

Author Comment

by:gsszuber
ID: 22607425
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.
0
 

Author Closing Comment

by:gsszuber
ID: 31501380
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.
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Permissions on Database 11 37
Can't connect to new installation of SQL Server 2016 6 30
Azure SQL Insert not working suddenly 11 22
ms sql last 8 weeks as columns 5 28
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now