By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,659 Members | 1,611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,659 IT Pros & Developers. It's quick & easy.

Update or Insert Query Help needed

P: 2
I have a table in the following format

ID RecType PKIdentifier RecordData

1 01 acc001 Text
2 03 NULL Text
3 04 NULL Text
4 01 acc002 Text
5 02 NULL Text
6 03 NULL Text
7 01 acc003 Text
8 03 NULL Text
9 04 NULL Text
10 01 acc004 Text

etc.

I need a query that will update all the NULL PKIdentifiers with the 01 RecType that precedes it until I get to the next 01. Example Records 2 and 3 should be updated with the PKIdentifier from Record 1 and Records 5 and 6 should be updated with the PKIdentifier from record 4 and so on. Basically RecType 01 are the parent Records and types 02 -04 are the child records until the next 01 record. I have approximately 20 Million rows to update.
What is the best way to do this?
Oct 8 '08 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
This will not really update your table but this should get your started. The last query can be used as subquery in an update statement or to create a temp table that you can analyze and use for your update.

Until someone has a better way, try doing this:

BACKUP YOUR TABLE FIRST


Expand|Select|Wrap|Line Numbers
  1. set nocount on
  2. declare @yourtable table (recnum int, RecType varchar(2), PKIdentifier varchar(15), RecordData varchar(10))
  3.  
  4. insert into @yourtable values (1, '01', 'acc001', 'Text')
  5. insert into @yourtable values (2, '03', NULL, 'Text')
  6. insert into @yourtable values (3, '04', NULL, 'Text')
  7. insert into @yourtable values (4, '01', 'acc002', 'Text')
  8. insert into @yourtable values (5, '02', NULL, 'Text')
  9. insert into @yourtable values (6, '03', NULL, 'Text')
  10. insert into @yourtable values (7, '01', 'acc003', 'Text')
  11. insert into @yourtable values (8, '03', NULL, 'Text')
  12. insert into @yourtable values (9, '04', NULL, 'Text')
  13. insert into @yourtable values (10, '01', 'acc004', 'Text') 
  14.  
  15. select * 
  16. from @YourTable where PKIdentifier is not null
  17.  
  18. select * 
  19. from @YourTable where PKIdentifier is null
  20.  
  21. select recnum, RecType, PKIdentifier, RecordData,
  22. newPKIdentifier = isnull((select top 1 PKIdentifier from @YourTable b where a.recnum > b.recnum and b.PKIdentifier is not null and a.pkidentifier is null order by b.recnum desc),a.pkidentifier)
  23. from @YourTable a
  24.  
Happy Coding.

-- CK
Oct 8 '08 #2

P: 2
Thanks. That's what I was looking for.
Oct 8 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.