Identifying/Updating multiple groups of null values in a table

jisoo411 used Ask the Experts™
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.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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...

FROM Table1;

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

FROM Table1;
Project Leader

Above solution will work if records with same office_id comes together in Surrogate_Key values. And as he mentioned, you will need to run it multiple times, if there are multiple records with NULL value appearing one after the other.

Below is a generic solution that will work irrespective of the number of NULL value records and for non-continuous Surrogate_Key values:
Update T1
set category_id = T2.category_id,
	order_id = T2.order_id 
From table1 T1, table1 T2
where T1.category_id is null
and T1.order_id is null
and T1.office_id = T2.office_id 
and T1.product_id = T2.product_id
and T2.surrogate_key = (Select MAX(surrogate_key) from table1 T3 
	where T3.office_id = T2.office_id and T3.product_id = T2.product_id 
	and T3.category_id is not null and T3.order_id is not null
	and T3.surrogate_key < T1.surrogate_key)

Open in new window

Again, the code assumes that both category_id and order_id columns will be NULL whenever one of them is NULL. If that is not the case, you need to break it into two separate updates - one for category_id and the other for order_id.

Harish's code looks good to me.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial