asked on

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.

tculler

I really don't see a simple way this can be done without using a primary key. I suggest you implement one ASAP.

countrymeister

ASKER

How about updating all the rows which mett the following qualification

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

Raja Jegan R

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

countrymeister

ASKER

rrjegan17:

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

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Raja Jegan R

ROW_NUMBER() is a function starting from SQL Server 2005.
Provided you that approach since SQL Server 2005 is mentioned in your zone.