Link to home
Start Free TrialLog in
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
Avatar of Tony303
Tony303
Flag of New Zealand image

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
Avatar of Harish Varghese
Harish Varghese
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Harish's code looks good to me.