Update statement SQL with case statement

user_10622238 0
Viewed 67

how to update values for following condition

CREATE TABLE T 
  ( 
     C VARCHAR(10) 
  ); 

INSERT INTO T 
VALUES      ('0000'), 
            ('123456789'),
            ('A'), 
            (''),
            ('    '), 
            (NULL);

I need to leave the NULL values or values which has 0000 as is and update the remaining to 1234.

expected output as below:

0000
1234
1234
1234
1234
NULL

I have tried with below statement but is there a better way to do this?

UPDATE T SET C=CASE WHEN C IS NULL then null
when C='0000' then '0000' else '1234' END
sql-server sql
3 Answers

just use where clause ,you can try like below

update T
set C='1234'
where C<>'0000'
user_68085830

You don't need to use case expression use where clause instead :

UPDATE t
     SET C = '1234'
WHERE C IS NOT NULL OR C <> '0000';
user_55294450

Simply

UPDATE T SET C='1234' where c <> '0000'

Will work since the null comparisons aren't true.

SQL>UPDATE T SET C='1234' where c <> '0000';

                  4 rows updated

SQL>select * from t;
C
==========
0000
1234
1234
1234
1234
-

                  6 rows found
user_37060160
Related