CODEWITHSUNDEEP
sqloracle-databaseplsql
bodzilla
bodzilla

Reputation: 69

Convert SQL to PL/SQL For "IF NOT EXISTS"

I have some SQL code that I want to convert into PL/SQL for Oracle, however I'm struggling to find a good alternative for the "IF NOT EXISTS" function. Can someone help?

Here's the SQL that needs converting:

IF NOT EXISTS (SELECT * FROM SEC_ConfigSetting WHERE ItemKey='Version')
    BEGIN
        INSERT INTO SEC_ConfigSetting
               ([VersionNo]
               ,[Section]
               ,[ItemKey]
               ,[ItemValue]
               ,[ItemType])
         VALUES
               (1,4,'Version','V1.4.0',0)
    END
ELSE
    BEGIN
        /* Update database version */
        UPDATE [SEC_ConfigSetting]
        SET [ItemValue] = 'V1.4.0'     
        WHERE ItemKey='Version'
    END

Upvotes: 0

Views: 209

Answers (1)

Kacper
Kacper

Reputation: 4818

What you're doing looks like you need merge

merge into SEC_ConfigSetting s
  using (select 1 VersionNo, 4 Section,'Version' ItemKey,'V1.4.0' ItemValue, 0 ItemType from dual) d
  on (s.ItemKey = d.ItemKey)
when matched then update set
  ItemValue = d.ItemValue
when not matched then    
  INSERT (VersionNo, Section,ItemKey,ItemValue,ItemType)
  VALUES (d.VersionNo, d.Section, d.ItemKey, d.ItemValue, d.ItemType);

And this can be used as a plain SQL or as part of PL/SQL script.

Upvotes: 4

Related Questions