CODEWITHSUNDEEP
oracle-databasesql-merge
Mikkel Voigt
Mikkel Voigt

Reputation: 31

Merging without source table Oracle SQL Developer

Im trying to merge data to a database and i cannot without a table and i would like not to create a temporary table just to be deleted afterwards

MERGE INTO target_table target
USING ('0101-2019' as date, 1515 as random_data, 9595 as more_random_data) source
ON (target.date= source.date) 
WHEN MATCHED THEN
    UPDATE  SET target.date = source.date,  
                    target.random_data = source.random_data, 
                    target.more_random_data = source.more_random_data
WHEN NOT MATCHED THEN
    INSERT  (date,  random_data, more_random_data)
    VALUES (source.date,  source.random_data, source.more_random_data);

For some reason i could find a method that would have worked if i did not ude Oracle SQL Developer but i cannot tranlate it to Oracle

MERGE target_table AS target
USING   (VALUES ('0101-2019', 1515, 9595))
     AS source(date, random_data, more_random_data)
ON target.date= source.date
WHEN MATCHED THEN
    UPDATE  SET target.date = source.date,
                    target.random_data = source.random_data
                    target.more_random_data = source.more_random_data
WHEN NOT MATCHED BY target THEN
    INSERT  (date, random_data, more_random_data)
    VALUES (source.date, source.random_data, source.more_random_data)
WHEN NO TMATCHED BY source THEN
    DELETE

Dont ask me where this would have worked, this is some i found from samples that i wrote to fit my situation, but since i use SQL Developer by Oracle it did not work for me.

Upvotes: 3

Views: 1856

Answers (1)

user330315
user330315

Reputation:

Oracle still doesn't support the standard VALUES row constructor. You need to use the workaround with select from dual:

USING (
   select '0101-2019' as date, 1515 as random_data, 9595 as more_random_data
   from dual
) source

Upvotes: 5

Related Questions