IS there a better way to update multiple columns of a table in sql

Is there a better way to do this?

I have to update multiple columns of table like below:

UPDATE tableA
       SET column1= '1234' WHERE column1 is NOT NULL;
UPDATE tableA
       SET column2= '1234' WHERE column2 is NOT NULL;
UPDATE tableA
        SET column3= '1234' WHERE column3 IS NOT NULL OR column3<>'0000';

5 answers

  • answered 2018-11-08 07:02 Zaynul Abadin Tuhin

    you can try like below

    UPDATE tableA
           SET column1= '1234' ,         
            column2= '1234',
            column3= '1234'
      WHERE (column2 is NOT NULL and  column1 is NOT NULL)       
              and ( column3 IS NOT NULL OR column3<>'0000')
    

  • answered 2018-11-08 07:05 fa06

    You can try using CASE WHEN

    UPDATE tableA
           SET column1= case when column1 is NOT NULL then '1234' end,
            column2= case when column2 is NOT NULL then '1234' end,
            column3= case when column3 is NOT NULL OR column3<>'0000' then '1234' end
    

  • answered 2018-11-08 07:07 Mayank Porwal

    Instead of 3 updates, you can do:

    UPDATE tableA
           SET column1= CASE WHEN column1 is NOT NULL THEN '1234' END, 
           SET column2= CASE WHEN column2 is NOT NULL THEN '1234' END,
           SET column3= CASE WHEN column3 IS NOT NULL OR column3<>'0000' THEN '1234' END
    

  • answered 2018-11-08 07:13 Salman A

    Combine all where clauses and update the columns by checking each condition:

    UPDATE tableA SET
        column1 = CASE WHEN column1 IS NOT NULL THEN '1234' ELSE column1 END,
        column2 = CASE WHEN column2 IS NOT NULL THEN '1234' ELSE column2 END,
        column3 = CASE WHEN column3 IS NOT NULL THEN '1234' ELSE column3 END
    WHERE (
        column1 IS NOT NULL OR
        column2 IS NOT NULL OR
        column3 IS NOT NULL
    )
    

    Note that column3 IS NOT NULL OR column3 <> '0000' is same as column3 IS NOT NULL because 0000 is not null.

  • answered 2018-11-08 12:42 Gordon Linoff

    I assume that your last condition should really be column3 IS NOT NULL AND column3 <> '0000'. The OR would not include the second condition.

    So I think you want:

    UPDATE tableA
        SET column1 = (CASE WHEN column1 IS NOT NULL THEN '1234' END),
            column2 = (CASE WHEN column2 IS NOT NULL THEN '1234' END),
            column3 = (CASE WHEN column3 IS NOT NULL AND column3 <> '0000' THEN '1234' END)
    
        WHERE (column1 IS NOT NULL) OR
              (column2 IS NOT NULL) OR
              (column3 IS NOT NULL AND column3 <> '0000');
    

    The WHERE clause doesn't affect the result set. It is only there to limit the number of rows being updated.