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
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

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Tony303
Tony303
Flag of New Zealand image

Harish's code looks good to me.
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo