Sql-server – Bulk Update Based Off Values In A Table

sql serversql server 2014t-sql

In Sql Server 2014 I need to run an Update statement that will update NULL values with values found previously in the table. To better illustrate, here is a very small subset of my data

Create Table #Booberry
(
  id int
  ,customer varchar(500)
  ,os varchar(500)
  ,ms varchar(500)
  ,st varchar (500)
)

Insert Into #Booberry Values
('1', 'Two', 'Win10', 'M1', 'Stop')
,('2', 'Four', 'Step', 'T23', 'Go')
,('3', 'Two', NULL, NULL, NULL)

(again very small subset for illustration purposes only). What I am needing to accomplish is for each row where OS is null, scan the table and determine if that customer has a previous value. I think, how this would take place to update the missing data for customer Two would be something like:

MAX([os]) As MaxOS = Win10
MAX([MS]) As MaxMS = M1
MAX([ST]) As MaxST = Stop

Find the MAX() value for the data points to update then use those in an actual update statement. Meaning that any other customer in my table with a value of Two should have the fields [os],[ms],[st] equal the values above.

How would I write an update query to accomplish this?

If further explanation is needed, I will be happy to provide.

Best Answer

If you are confident that there can only ever be at most two rows for any given customer, then you can do this:

;WITH x AS 
(
 SELECT id,customer,os,ms,st,
  previous_os = LAG(os,1,NULL) OVER (PARTITION BY customer ORDER BY id),
  previous_ms = LAG(ms,1,NULL) OVER (PARTITION BY customer ORDER BY id),
  previous_st = LAG(st,1,NULL) OVER (PARTITION BY customer ORDER BY id)
 FROM #Booberry
)
UPDATE x SET
  os = COALESCE(os,previous_os),
  ms = COALESCE(ms,previous_ms),
  st = COALESCE(st,previous_st);

But I don't know how you can be so sure. This won't work very well if you have a case where there are three rows for a customer, the most recent row is all NULLs, the one before that has NULL for st, and the one before that has NULL for os. I think we need better requirements and how you want the results to look in the event of edge cases.

Related Question