help with a complex update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help with a complex update

Author

Topic

00kevin
Yak Posting Veteran

78 Posts

Posted - 2009-04-30 : 14:33:57

Basically, I need to fix a table that contains a hierarchy.

Currently the table contains bad data that looks like this.



Table:Submission

TxnDate             SubmissionId    OrigSubmissionId    AgrId
2004-11-10          23065284        Null                500
2004-11-11          93785699	    23065284            500
2004-11-12          94569457        23065284            500
2004-11-13          96175712	    23065284            500
2004-11-14          97540926	    23065284            500

2007-07-01          95894194	    Null
2007-07-02          101168819	    95894194            504	
2007-07-03          101220089	    95894194            504

What I need to do is update the submission table so that the bad records look like the following.


2004-11-10          23065284        Null
2004-11-11          93785699	    23065284            500
2004-11-12          94569457        93785699            500        
2004-11-13          96175712	    94569457            500        
2004-11-14          97540926	    96175712            500

2007-07-01          95894194	    Null
2007-07-02          101168819	    95894194            504
2007-07-03          101220089	    101168819           504

Thus far I have managed to identify all the records in the Submission table that have dups and I have a temp table with this information.


#dups
OrigSubmissionId   DupCount    AgrId
23065284           4             500
95894194           2             504

Thanks for any help you can provide.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-30 : 18:10:15

I don't believe that someone can understand the problem with the given information.
What is the problem?
What is the rule to modify the data?
Sorry
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-30 : 18:24:25

Assuming AgrID is your common column:

UPDATE S SET OrigSubmissionId=(select min(SubmissionId) 
from Submission where AgrID=S.AgrID AND SubmissionId>S.SubmissionId)
FROM Submission S

Warning: this has not been tested.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-30 : 18:48:21

quote:
Originally posted by webfred

I don't believe that someone can understand the problem with the given information.

THX rob!
I just posted this to dare someone to do something

No, you're never too old to Yak'n'Roll if you're too young to die.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-30 : 18:57:42

Dammit, and I fell for it!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources