Update the values of column in sequence

Question

I have table in which there are 8 columns, now what I want is If there is value in column1,column2 and then column3,column4 are null and again column5,column6 value so those column5,6 should be update in column3,and column4. and so on for all different cases. Is that possible to resolved it with single query without using stored procedure or any iterative process.thanks

If you only have 6 columns, just write a case statement with all the combinations. If the number of columns is large, then you could build something with unpivot + row_number — James Z
Rohit, there can be multiple scenarios. 1) I have 12 columns and it is possibility that first 11 are blank and data present in 12th column than i need to move it to column1 2) First 8 columns are blank and 9 has value then 10 is blank and 11,12 has value. 3) column 1,2 has value 3,4 has no value then again 5,6 has value. ... so this n kind of scenarios possible. so each time I have to check if the very starting column has value or not if not then move the value to that column and so on. — dhaval panchal

EstevaoLuis EstevaoLuis · Accepted Answer · 2017-06-10T07:10:44

Try this:

UPDATE a
SET a.column3 = b.column5, a.column4 = b.column6
FROM Table a 
INNER JOIN myTable b
ON a.Id = b.Id
WHERE column1 IS NOT NULL AND
      column2 IS NOT NULL AND
      column3 IS NULL AND
      column4 IS NULL AND
      column5 IS NOT NULL AND
      column6 IS NOT NULL

Update the values of column in sequence

1 Answers