SQL syntax help

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.
LVL 1
countrymeisterAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
chapmandewConnect With a Mentor Commented:
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
 
tcullerCommented:
I really don't see a simple way this can be done without using a primary key. I suggest you implement one ASAP.
0
 
countrymeisterAuthor Commented:
How about updating all the rows which mett the following qualification

select count(*) , OrderID from #tmpOrders
group by OrderID having count(*) > 1
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
countrymeisterAuthor Commented:
rrjegan17:

I tried your query it states row-number() is not a valid function, I am using SQL Server 2000
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
ROW_NUMBER() is a function starting from SQL Server 2005.
Provided you that approach since SQL Server 2005 is mentioned in your zone.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.