Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 188
  • Last Modified:

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.
0
countrymeister
Asked:
countrymeister
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
countrymeisterAuthor Commented:
rrjegan17:

I tried your query it states row-number() is not a valid function, I am using SQL Server 2000
0
 
chapmandewCommented:
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
 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now