Update or Insert Query Help needed

[cdex33]

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?

[ck9663]

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

set nocount on
declare @yourtable table (recnum int, RecType varchar(2), PKIdentifier varchar(15), RecordData varchar(10))
 
insert into @yourtable values (1, '01', 'acc001', 'Text')
insert into @yourtable values (2, '03', NULL, 'Text')
insert into @yourtable values (3, '04', NULL, 'Text')
insert into @yourtable values (4, '01', 'acc002', 'Text')
insert into @yourtable values (5, '02', NULL, 'Text')
insert into @yourtable values (6, '03', NULL, 'Text')
insert into @yourtable values (7, '01', 'acc003', 'Text')
insert into @yourtable values (8, '03', NULL, 'Text')
insert into @yourtable values (9, '04', NULL, 'Text')
insert into @yourtable values (10, '01', 'acc004', 'Text') 
 
select * 
from @YourTable where PKIdentifier is not null
 
select * 
from @YourTable where PKIdentifier is null
 
select recnum, RecType, PKIdentifier, RecordData,
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)
from @YourTable a
 

Happy Coding.

-- CK

[cdex33]

Thanks. That's what I was looking for.