CODEWITHSUNDEEP
sql-servert-sql
Randy B.
Randy B.

Reputation: 453

Conditional Update Statement T-SQL

I'm trying to update a very wide table in SQL that has misused a few columns for years resulting in some very messy data. I want to write a conditional update statement to shift data from one column to another as they are all basically one column off where they are supposed to be if they are not null.

UPDATE SOME_TABLE
SET Data1 = (CASE WHEN Data1 IS NOT NULL THEN Data1 = Data0 ELSE Data1),
SET Data2 = (CASE WHEN Data2 IS NOT NULL then Data2 = Data1 ELSE Data2),
SET Data3 = (CASE WHEN Data3 IS NOT NULL then Data3 = Data2 ELSE Data3)
GO

How can I construct this to "shift" data conditionally as I am trying to do?

EXAMPLE - Data0 is ALWAYS NULL (Was never used properly)

So Data is always like so: NULL || ABC || XHG || XYZ

In this case, I need to move each of these columns one to the left in a given row. The data within is all VARCHAR.

Upvotes: 2

Views: 9641

Answers (3)

paparazzo
paparazzo

Reputation: 45106

UPDATE SOME_TABLE
  SET Data1 = isnull(Data1, Data0),
      Data2 = isnull(Data2, Data1),
      Data3 = isnull(Data3, Data2)
where Data1 is null 
   or Data2 is null
   or Data3 is null

if you don't have a lot a nulls this might be faster

UPDATE SOME_TABLE
  SET Data1 = Data0 
where Data1 is null and Data0 is not null;
UPDATE SOME_TABLE
  SET Data2 = Data1 
where Data2 is null and Data1 is not null;
UPDATE SOME_TABLE
  SET Data3 = Data2 
where Data3 is null and Data2 is not null;

if you have back to back null then you need another approach or you could just run the statements multiple times

Upvotes: 0

user330315
user330315

Reputation:

So I need Data 1 to move to Data 0 when not null. Seems like I am missing something

You need to update data0 with the value of data1 not the other way round:

UPDATE SOME_TABLE
  SET Data0 = CASE WHEN Data1 IS NOT NULL THEN Data0 ELSE Data0 END,
      Data1 = CASE WHEN Data2 IS NOT NULL then Data1 ELSE Data1 END,
      Data2 = CASE WHEN Data3 IS NOT NULL then Data2 ELSE Data2 END;

From your description it sounds as if actually don't need the case at all. Apparently you want to shift all columns if data0 is null:

UPDATE SOME_TABLE
  SET Data0 = Data1,
      Data1 = Data2,
      Data2 = Data3
WHERE data0 IS NULL; -- only shift if data0 is "empty"

Upvotes: 4

sagi
sagi

Reputation: 40491

You are just not using the proper syntax for a join and missing an END after each case :

UPDATE SOME_TABLE
     SET Data1 = CASE WHEN Data1 IS NOT NULL THEN  Data0 ELSE Data1 end,
         Data2 = CASE WHEN Data2 IS NOT NULL then  Data1 ELSE Data2 end,
         Data3 = CASE WHEN Data3 IS NOT NULL then  Data2 ELSE Data3 end

Although this update doesn't makes much sense.. if the column is not null, update it and if it is null, keep it null? Are you sure thats what you want to achieve?

Upvotes: 2

Related Questions