CODEWITHSUNDEEP
sqloracle-databasestored-proceduresdatabase-migrationsql-scripts
Xavier DSouza
Xavier DSouza

Reputation: 2931

SQL or procedure to conditionally insert or update in Oracle database

I don't have much of a hands-on in SQL and procedures. I need a migration script wherein I need to update or insert a table based on data in other two tables.

Organization:
id   name  pid
1   org1  null
2   org2  null
3   org3  1
4   org4  2

Org_Channel:
org_id   channel
1         CH_100
2         CH_101

Organization table has a parent-child self referenced relation. (pid null in case of parent). Org_Channel is a mapping table for parent organizations only.

Now I have a third table Org_Settings in which I need to migrate the data based on the above two tables. Each record here indicates a organization id, a setting name which is prefixed by channel name(for child org.this will be parent org.channel), and a flag. I need a migration SQL script / procedure for a setting Sign_On to be enabled as 'Y' for every organization

The current table is something like this:

Org_Settings:
org_id   s_name              enabled
1       CH_100_Sign_On    N
1       CH_100_X_O          Y
4       CH_101_Sign_On   Y


Now Org_Settings may or may not contain entry for each org. Also I need to migrate such that, if entry is present for Sign_On then need to update enabled = Y. Such that the result would be:

Org_Settings:
org_id   s_name              enabled
1       CH_100_Sign_On    Y
2       CH_101_Sign_On    Y
3       CH_100_Sign_On    Y
4       CH_101_Sign_On    Y


I could think of pseudo code like:

for i in each org
    var pid = getPid(i)
    var id = (null == pid) ? i : pid
    var channel = getChannel(id);
    var sname = channel + "_Sign_On"
    if(settingsEntryExists(i, sname))
        updateSettingsEnable(i, sname, 'Y')
    else
        insertSettings(i, sname, 'Y')

Upvotes: 0

Views: 441

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31706

Try this MERGE INTO statement. I did not understand the logic behind updating to 'Y' if entry exists and also inserting 'Y' if it does not exist. Isn't it same as simple insert?. or am I missing something? . You may tweak this query slightly if there is some missing info to clarify my question above.

SQLFiddle

MERGE 
INTO    Org_Settings d 
USING   ( select 
                org.id org_id, 
                ch.channel|| 
                '_Sign_On' s_name , 
                'Y'        enabled 
        FROM 
                Organization org
        JOIN    Org_Channel  ch ON NVL(org.pid,id) = ch.org_id
        )s 
ON ( d.org_id  = s.org_id 
                AND d.s_name = s.s_name )
WHEN MATCHED THEN 
UPDATE SET d.enabled = 'Y' 
WHEN NOT MATCHED THEN 
INSERT
        (org_id,s_name,enabled 
        ) VALUES 
        (s.org_id,s.s_name,s.enabled 
        );

Upvotes: 1

Related Questions