467,915 Members | 1,283 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,915 developers. It's quick & easy.

Update or Insert Query Help needed

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
  • viewed: 1393
Share:
2 Replies
ck9663
Expert 2GB
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
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.

Similar topics

4 posts views Thread by Rustam Bogubaev | last post: by
16 posts views Thread by Philip Boonzaaier | last post: by
2 posts views Thread by Joshua Moore-Oliva | last post: by
4 posts views Thread by Daniel | last post: by
13 posts views Thread by Neil | last post: by
3 posts views Thread by Michel Esber | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.