Avatar of jisoo411
jisoo411
 asked on

Identifying/Updating multiple groups of null values in a table

Hello everyone,

I'm having some trouble with a particular problem and I couldn't find any topics similar to this.  I have the following table:

create table1 (
  surrogate_key bigint identity(1,1),
  category_id int,
  office_id int,
  order_id int,
  product_id int,
  seq_num int,
  date_created datetime
)

Open in new window


Table data looks like this:
1  10  1  100  25  1  2012-01-01 12:00:00
2  10  1  100  25  2  2012-01-01 12:00:00
3  null  1  null  25  3  2012-01-03 04:00:00
4  null  1  null  25  4  2012-01-04 02:00:00
5  11  1  101  25  5  2012-01-07 05:00:00
6  null  1 null  25  6  2012-01-08 12:00:00
7  12  2  15  30  1  2012-02-01 12:00:00  
8  null  2  null  30  2  2012-02-02 4:00:00
9  12  2  15  30  3  2012-02-03 01:00:00

Open in new window


The sequence number column represents the order of individual units of that product_id within that office_id in chronological order of entry.

What I'm trying to do is for rows where the category_id and order_id are missing, I want to fill it in with values from the latest row preceding it, matching on office_id and product_id.  I've been working on this for a while and am stumped on how I can identify those preceding rows to grab the values for an update.  Any help is appreciated.

Thanks,
Glen
Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
Tony303

8/22/2022 - Mon
Tony303

Just make a copy so you can test this....

It is fine until you get 2 null lines in sequence, then you need to run it twice...



SELECT *
FROM Table1;

UPDATE t2
SET t2.Category_id = t1.Category_id,
      t2.Order_id = t1.Order_id

FROM Table1 t1, (Select *
FROM Table1) t2
WHERE t1.Surrogate_key = t2.Surrogate_key -1
AND t1.Product_id = t2.Product_id
AND t1.office_id = t2.office_id
AND t2.Category_id IS NULL AND t2.Order_id IS NULL


SELECT *
FROM Table1;
ASKER CERTIFIED SOLUTION
Harish Varghese

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Tony303

Harish's code looks good to me.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23