SQL : Updating a value for Column1 and moving the old value of the Column1 to Column2 at the same ti-CodePudding

I have a Table which I would like the columns in it to updated based on 3 situations. This is how the table looks like:

Column1 = slitting_win
Column2 = slitting_win2

Situation 1: When the Column1 is NULL or having the value of 0, it should update the value of the Column1 without doing anything to Column2 .

Situation 2: When the Column1 is updated with the same value, then the value in both Column1 and Column2 shall remain the same.

Situation 3: When the Column1 is updated with a new value, the old value from Column1 shall go to Column2.

Desired result:

Situation 1:

Entered value for Column1: 60

Column1 : 60 (Previously assuming that the column is NULL)

Column2: 80

Situation 2:

Entered value for Column1: 60

Column1 : 60 (No changes)

Column2: 80 (No changes)

Situation 3:

Entered value for Column1: 70

Column1 : 70 (Previously was 60)

Column2: 60 (Previously was 80)

This is my query right now:

DECLARE @select_slitting INT
SET @select_slitting = (select slitting_win from daily_wastage_follow_up where date = @date)
IF @select_slitting = '' OR @select_slitting IS NULL 
    update daily_wastage_follow_up set slitting_win = @slt where date = @date
ELSE IF @select_slitting IN (@select_slitting)
    update daily_wastage_follow_up set slitting_win = @slt where date = @date
ELSE IF @select_slitting NOT IN (SELECT slitting_win2 FROM daily_wastage_follow_up WHERE date=@date)
    update daily_wastage_follow_up set slitting_win2 = @select_slitting,slitting_win = @slt where date = @date

The problem right now is with the Situation 3 which I cannot think of a way to move the previous value of Column1 to Column2 when updating the old value of Column1. Any help is greatly appreciated! Thanks.

CodePudding user response：

you can use case expression to evaluate the 3 situations. #1, #2, #3 are your required situations

update d
set    slitting_win  = case when d.slitting_win is null or d.slitting_win = 0 -- #1
                            then @slt
                            when d.slitting_win = @slt -- #2
                            then d.slitting_win                             
                            else @slt  -- #3
                            end,
       slitting_win2 = case when slitting_win is null or slitting_win = 0 -- #1
                            then d.slitting_win2
                            when d.slitting_win = @slt -- #2
                            then d.slitting_win2
                            else d.slitting_win -- #3
                            end
from   daily_wastage_follow_up d
where  d.[date] = @date

CodePudding user response：

As I understand your question, following logic should be used:

Column1 should always be updated with the new update value. Maybe the only exception is that the updateValue itself must not be null or 0. I assume this exception must be set, if not, you can remove it. If the new value is the same as the old one, so what? We can just update anyway in this case.

Column2 should only be updated to the previous value of column1 if this value was not null, not 0 and was different to the updateValue. In all other cases, it should just keep its previous value.

If this is correct so far, following logic will work correctly:

DECLARE @updateValue INT;
SET @updateValue = 60;
IF @updateValue IS NOT NULL AND @updateValue <> 0
  UPDATE yourtable SET 
  column2 = 
    CASE 
    WHEN column1 IS NULL OR column1 IN (0,@updateValue) THEN column2 
    ELSE column1 END,
  column1 = @updateValue;

Of course, a WHERE clause will likely be required. And of course, you will likely fill the updateValue using a query instead of a hard coded value. So you will adjust this to your situation.

I was using exactly these column names and didn't use a WHERE clause or a query to fetch the updateValue in my example to better point out the logic which should be applied. This makes it easier if someone has a similar task because (s)he doesn't need to think about your column names.

We can try out here this is doing what it should: db<>fiddle