Solved

SQL UPDATE statement not working as expected

Posted on 2016-08-11
8
38 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
8 Comments
 
LVL 65

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 46

Accepted Solution

by:
Vitor Montalvão earned 500 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 142

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
 
LVL 69

Expert Comment

by:ScottPletcher
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 7

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 24

Expert Comment

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

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

911 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

23 Experts available now in Live!

Get 1:1 Help Now