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:
Table data looks like this:
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
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
)
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Harish's code looks good to me.
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;