Solved

SQL syntax help

Posted on 2009-05-06
6
183 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to set UNMASK permission only to only one table in SQL Server 2016 ? 11 100
Need help how to find where my error is in UFD 6 40
sql help 2 46
Where on a calculated field 1 22
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

828 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