Solved

SQL syntax help

Posted on 2009-05-06
6
181 Views
Last Modified: 2012-05-06
I have a temp table tmpOrders with the following columns

OrderID int
VersionNumber int
PrevOrderID int null

I need to update the column PrevOrdrID from the table where there is more than one row with the same OrderID, but not update the first row which has the same OrderID

so if it holds the following data

OrderID Version PrevOrderID
1             0            NULL
1             2            NULL
1             3            NULL
2             0           NULL
3             1           NULL
3             4           NULL
4             2          NULL

The result of the update should be

OrderID Version PrevOrderID
1             0            NULL
1             2            1
1             3            1
2             0           NULL
3             1           NULL
3             4           3
4             2          NULL

So 1 0 NULL is not updated and 3 1 NULL is not updated because this is the first row, that qualifies for where the count(*), OrderID group by OrderID having count(*) > 1

2 0 null and 4 2 null do not meet the criteria.
0
Comment
Question by:countrymeister
6 Comments
 
LVL 9

Expert Comment

by:tculler
ID: 24320966
I really don't see a simple way this can be done without using a primary key. I suggest you implement one ASAP.
0
 
LVL 1

Author Comment

by:countrymeister
ID: 24321023
How about updating all the rows which mett the following qualification

select count(*) , OrderID from #tmpOrders
group by OrderID having count(*) > 1
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24321094
Hope this helps:
update tmpOrders 

set PrevOrderID = t2.PrevOrderID

from tmpOrders t1, (

Select OrderID, Version

from (

select OrderID, Version, row_number() over ( partition by OrderID, Version order by OrderID, Version) rnum

from tmpOrders ) temp

where rnum >1) t2

where t1.OrderID = t2.OrderID

and t1.Version = t2.Version

Open in new window

0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 1

Author Comment

by:countrymeister
ID: 24321603
rrjegan17:

I tried your query it states row-number() is not a valid function, I am using SQL Server 2000
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 250 total points
ID: 24321631
update t
set prevorderid = a.orderid
from tmporders t
join (
select orderid, min(version) as minver
from tmporders
group by orderid
having count(*) > 1
) a on t.orderid = a.orderid and t.version > a.minver
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24322882
ROW_NUMBER() is a function starting from SQL Server 2005.
Provided you that approach since SQL Server 2005 is mentioned in your zone.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

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

21 Experts available now in Live!

Get 1:1 Help Now