Solved

SQL UPDATE statement not working as expected

Posted on 2016-08-11
8
40 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 48

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 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 69

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 8

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 28

Expert Comment

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

Expert Comment

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

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

789 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