SQL - how to update two row table with one SQL statement

Question

I have this Oracle table which is used to store application settings.

enter image description here

This is the SQL script that it's used to create the table:

CREATE TABLE "GLOBALSETTINGS"(
  "SettingName" Varchar2(40 ) NOT NULL,
  "SettingValue" Varchar2(40 )
)
/

-- Add keys for table GLOBALSETTINGS

ALTER TABLE "GLOBALSETTINGS" ADD CONSTRAINT "Key14" PRIMARY KEY ("SettingName")
/

ALTER TABLE "GLOBALSETTINGS" ADD CONSTRAINT "SettingName" UNIQUE ("SettingName")
/

This is the SQL statement that I want to use:

UPDATE GLOBALSETTINGS
SET settingValue =
  CASE
    WHEN settingName = 'SessionTTL'
    THEN '30'
    WHEN settingName = 'MaxUsersActive'
    THEN '40'
    ELSE settingValue
  END
WHERE settingName IN ('SessionTTL', 'MaxUsersActive');

But When I run it in SQL developer I get this error message:

Error starting at line 1 in command:
UPDATE GLOBALSETTINGS 
   SET settingValue = case 
                        when settingName = 'SessionTTL'  then '30' 
                        when settingName = 'MaxUsersActive'  then '40' 
                        else settingValue
                      end
WHERE settingName in ('SessionTTL', 'MaxUsersActive')
Error at Command Line:7 Column:6
Error report:
SQL Error: ORA-00904: "SETTINGNAME": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

How I can fix the problem?

Best wishes Peter

Your question is entirely misguided because you have failed to understand the error message. You frame it as a programming or syntax question but the message clearly states INVALID IDENTIFIER. This is because of your ill-advised use of mixed case ovject names in double quotes. See this question for more details: http://stackoverflow.com/questions/6027961/ora-00904-invalid-identifier/6030439#6030439 — APC, Mar 16 '12 at 16:51

score 8 · Accepted Answer · 2012-03-16T17:31:50.050

8

As you created the table columns using double quotes they are now case-sensitive.

So you have to use quotes all the time.

UPDATE GLOBALSETTINGS
SET "SettingValue"  =
  CASE
    WHEN "SettingName" = 'SessionTTL' THEN '30'
    WHEN "SettingName" = 'MaxUsersActive' THEN '40'
    ELSE "SettingValue" 
  END
WHERE "SettingName"  IN ('SessionTTL', 'MaxUsersActive');

Please re-read the manual regarding SQL identifiers.

http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements008.htm#i27570

Especially the paragraph:

Oracle does not recommend using quoted identifiers for database object names.

edited Mar 16 '12 at 17:31

answered Mar 16 '12 at 16:36

Thank you for the answer! one more thing do I need "ELSE "SettingValue"" I think that I don't need this. – Peter Penzov Mar 16 '12 at 17:11
I finally made it work: http://pastebin.com/eFTgBfK4 Do I really need WHERE "SettingValue" IN ('SessionTTL', 'MaxUsersActive'); – Peter Penzov Mar 16 '12 at 17:27
2

Sorry, I used the wrong where clause. See my edit (but honestly if you had tried understanding the statement you should have noticed my copy & paste error for the WHERE clause). And yes, I would highly recommend you use the WHERE clause. – Mar 16 '12 at 17:32
Can I remove "ELSE "SettingValue" " Is this needed? – Peter Penzov Mar 16 '12 at 17:44

SQL - how to update two row table with one SQL statement

1 Answers1