CODEWITHSUNDEEP
sqloracle-databasesql-update
john smith
john smith

Reputation: 27

Oracle sql, update multiple rows using CASE

I'm trying to update multiple rows using CASE. The code below

UPDATE GRIDCOLUMNS  
SET TYPE= CASE  
  WHEN (Fieldname = 'AccountNum' AND ID = 19337) THEN 6
  WHEN (Fieldname = 'AccountNum' AND ID = 19339) THEN 6 
  WHEN (Fieldname = 'AccountNum' AND ID = 19380) THEN 6
  WHEN (Fieldname = 'AccountNum' AND ID = 19419) THEN 6
  END;

When I run it, I get the following error:

Error starting at line 1 in command:
UPDATE GRIDCOLUMNS  
SET TYPE= CASE  
  WHEN (Fieldname = 'AccountNum' AND ID = 19337) THEN 6
  WHEN (Fieldname = 'AccountNum' AND ID = 19339) THEN 6 
  WHEN (Fieldname = 'AccountNum' AND ID = 19380) THEN 6
  WHEN (Fieldname = 'AccountNum' AND ID = 19419) THEN 6
  END
Error report:
SQL Error: ORA-01407: cannot update ("WEB"."GRIDCOLUMNS"."TYPE") to NULL
01407. 00000 -  "cannot update (%s) to NULL"
*Cause:    
*Action:

What could be the problem?

Upvotes: 0

Views: 916

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Don't use case. Instead:

UPDATE GRIDCOLUMNS  
    SET TYPE = 6
    WHERE Fieldname = 'AccountNum' AND ID IN (19337, 19339, 19380, 19419);

This is much more efficient. If you really need to use CASE, then you need an ELSE:

UPDATE GRIDCOLUMNS  
    SET TYPE = (CASE WHEN Fieldname = 'AccountNum' AND ID IN (19337, 19339, 19380, 19419) THEN 6
                     ELSE TYPE
                END);

Otherwise, the non-matching rows get set to NULL. But really, use the WHERE.

Upvotes: 2

Related Questions