Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL UPDATE statement not working as expected

Posted on 2016-08-11
8
Medium Priority
?
45 Views
Last Modified: 2016-09-10
We run the following statement and even though the correct set of records was being chosen by the FROM statement the SET statement was only working on random records. Why?

UPDATE tblOrder
SET tblOrder.Archive = 0
FROM            dbo.tblAllocationDetail INNER JOIN
                         dbo.tblOrder ON dbo.tblAllocationDetail.lngInvoiceTanID = dbo.tblOrder.lngOrderID
WHERE        (dbo.tblAllocationDetail.lngAllocationID IN
                             (SELECT        tblAllocationDetail_1.lngAllocationID
                               FROM            dbo.tblOrder AS tblOrder_1 INNER JOIN
                                                         dbo.tblAllocationDetail AS tblAllocationDetail_1 ON tblOrder_1.lngOrderID = tblAllocationDetail_1.lngInvoiceTanID
                               WHERE        (tblOrder_1.Archive = 0)))

AND (dbo.tblOrder.Archive = 1)
0
Comment
Question by:Adapt2NL
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41752341
You're goign to have to define 'correct set' and 'random records' for us, preferably with some sample data, as experts here cannot connect to your data source(s) and run queries so it is not obvious to us.

Offhand the T-SQL syntax looks correct, athought it does seem odd that tables tblOrder and tblAllocationDetail are used both in the JOIN clause and in a WHERE..IN subquery.
0
 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 2000 total points (awarded by participants)
ID: 41752353
Why do you need the subselect?
UPDATE tblOrder
SET tblOrder.Archive = 0
FROM dbo.tblAllocationDetail 
    INNER JOIN dbo.tblOrder ON dbo.tblAllocationDetail.lngInvoiceTanID = dbo.tblOrder.lngOrderID
WHERE dbo.tblOrder.Archive = 1

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41752365
I think you should read up this article to rewrite your sql:
https://www.experts-exchange.com/articles/1517/UPDATES-with-JOIN-for-everybody.html
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 41752898
Always UPDATE a table alias -- rather than the full table name -- when a JOIN is used in the UPDATE; otherwise SQL can get "confused" about what to update.  For example, see below.  Besides, table aliases make the code vastly easier to read and maintain anyway.


UPDATE o
SET Archive = 0
FROM            dbo.tblAllocationDetail ad INNER JOIN
                         dbo.tblOrder o ON ad.lngInvoiceTanID = o.lngOrderID
WHERE        (ad.lngAllocationID IN
                             (SELECT        ad_1.lngAllocationID
                               FROM            dbo.tblOrder AS o_1 INNER JOIN
                                                         dbo.tblAllocationDetail AS ad_1 ON o_1.lngOrderID = ad_1.lngInvoiceTanID
                               WHERE        (od_1.Archive = 0)))
AND (o.Archive = 1)
2
 
LVL 10

Expert Comment

by:Jason clark
ID: 41753365
take a look at this helpful article Update from Select in SQL Server
0
 

Author Comment

by:Adapt2NL
ID: 41753377
Thank you for your answers. We eventually discovered that the updates themselves changed the data in such a way that the result set became different to expectations. So it turns out that the SQL statements were OK; our complete understanding of the data was not!
0
 
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41766494
@Author - Could you please close this.
0
 
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41792465
Question is closed.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

704 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