Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL syntax help

Posted on 2009-05-06
6
Medium Priority
?
187 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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 1000 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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

604 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