Solved

SQL UPDATE statement not working as expected

Posted on 2016-08-11
8
41 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 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 49

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 9

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Upgrading to SQL Server 2015 Express 2 31
T-SQL: Need Group By to use "fuzzy logic"?? 3 18
SQL 2012 Instance Problem 3 55
SQL query 7 14
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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

756 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