Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL UPDATE statement not working as expected

Posted on 2016-08-11
8
Medium Priority
?
46 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 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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 35

Expert Comment

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

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

877 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