0

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.

  • 1
    What does "previous value" mean? What if there are two rows for customer 'Two' with values, how do I pick? WHat if one of those rows has a value for os and st but not ms, and the other row has values for os and ms but not for st? Need more than three rows of sample data or a better description so we can understand what you're actually trying to achieve. – Aaron Bertrand Jun 21 '16 at 14:19
  • @AaronBertrand by previous value I meant the one already input in the table. If a customer has multiple values they will be the same value, so you can pick either one – NadineSmithJonesPicard Jun 21 '16 at 14:25
  • How are you so confident that if there is more than one "previous" row for a customer, that both rows will have identical values? – Aaron Bertrand Jun 21 '16 at 14:26
  • @AaronBertrand --- The data was automatically populated via an interface we had. However, the interface went down and now we have a great number of customers to update. – NadineSmithJonesPicard Jun 21 '16 at 14:29
2

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.

  • There can be multiple rows for customer. But only one unique set of values for os, ms, st – NadineSmithJonesPicard Jun 21 '16 at 14:33
  • same principle, slight tweak of your syntax got me down the right path! Thanks. – NadineSmithJonesPicard Jun 21 '16 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.