CODEWITHSUNDEEP
sqloracle-database
o1iver
o1iver

Reputation: 1805

SQL: update if exists, else insert... but for multiple rows with different values

I would like to combine insert/update with a case statement, meaning that I want to insert the row if it doesnt exist, update it if it does, but in both cases with different values (when updating it depends on the id) and when inserting, well then I set it...

And this should happen in a single SQL statement :-)

In a nutshell: I am trying to combine these two statements into a single one:

1). Insert/Update:

MERGE INTO table_name USING dual ON (id='{id}')
WHEN MATCHED     THEN UPDATE SET {col1}='{val1}', {col2}={val2}
WHEN NOT MATCHED THEN INSERT ({id}, {col1}, {col2}) VALUES ('{id}', '{val1}', {val2})

2). Update different values with case:

UPDATE SIGNALVALUE
    SET
        SIGNUMVALUE = CASE SIGID
        WHEN 49634 THEN 1.1
        WHEN 49674 THEN 2.2
        WHEN 49675 THEN 1.8
        END,
        UPDATETIME = CASE SIGID
        WHEN 49634 THEN TO_TIMESTAMP_TZ('2011-03-18 18:24:56:00', 'YYYY-MM-DD HH24:MI:SS:FF6 TZR')
        WHEN 49674 THEN TO_TIMESTAMP_TZ('2011-03-18 18:24:56:00', 'YYYY-MM-DD HH24:MI:SS:FF6 TZR')
        WHEN 49675 THEN TO_TIMESTAMP_TZ('2011-03-18 18:24:56:00', 'YYYY-MM-DD HH24:MI:SS:FF6 TZR')
        END
   WHERE SIGID IN (49634, 49674, 49675)

These statements are just examples. In reality there are many more rows to insert/update and this happens frequently so I am trying to minimize the amount of queries.

Also I cannot do update and then see how many rows were affected and then insert the ones that werent because I will not know the IDs of the rows that need to be inserted vs the ones that need to be updated. At least as far as I understand...please correct me!

Upvotes: 0

Views: 4618

Answers (2)

o1iver
o1iver

Reputation: 1805

MERGE INTO SIGNALVALUE USING (
SELECT 49674 as SIGID,
        TO_TIMESTAMP_TZ('2011-03-18 18:24:56:00', 'YYYY-MM-DD HH24:MI:SS:FF6 TZR') AS UPDATETIME,
        '777' as SIGNUMVALUE
        FROM dual
        UNION ALL
SELECT 49675 as SIGID,
        TO_TIMESTAMP_TZ('2011-03-18 18:24:56:00', 'YYYY-MM-DD HH24:MI:SS:FF6 TZR') AS UPDATETIME,
        '777' as SIGNUMVALUE
        FROM dual
        UNION ALL
SELECT 49676 as SIGID,
        TO_TIMESTAMP_TZ('2011-03-18 18:24:56:00', 'YYYY-MM-DD HH24:MI:SS:FF6 TZR') AS UPDATETIME,
        '777' as SIGNUMVALUE
        FROM dual
) n
ON(SIGNALVALUE.SIGID = n.SIGID)
WHEN NOT MATCHED THEN
    INSERT (SIGID, SIGNUMVALUE, UPDATETIME) VALUES (n.SIGID, n.SIGNUMVALUE, n.UPDATETIME)
WHEN MATCHED THEN
    UPDATE SET SIGNUMVALUE=n.SIGNUMVALUE, UPDATETIME=n.UPDATETIME

Upvotes: 0

Ronnis
Ronnis

Reputation: 12833

It doesn't have to be a table in the using clause, you can also use a SQL query.
At least that is what I think you are after :)

merge 
 into target_table
using (select case when ... then ... else ... end as id
         from dual
      ) source_table
    on(target_table.id = source_table.id)
when matched then
   update 
      set ...
when not matched then
   insert (...)
   values (...)

Let me know if that helps. If so, I can probably help write the final query as well.

Upvotes: 3

Related Questions