1

I could not find anything online about this problem. It seems ORACLE has a similar issue, which is handled using DUAL, as suggested in this StackOverflow answer.

But how do we do the same in Exasol? According to their documentation, you need to MERGE using a secondary table.

I tried to do the same approach as on Oracle, to no avail:

MERGE INTO TEST.TABLE USING SYS.DUAL ON "COLUMN_1" = "foo"
WHEN MATCHED THEN UPDATE SET "COLUMN_2" = "quux"
WHEN NOT MATCHED THEN INSERT ("COLUMN_1", "COLUMN_2") VALUES ("foo", "bar")

[2021-12-15 11:22:47] [0A000] Feature not supported: Merge using a system table as source that is no view. (Session: 1719203050222845952)

Is it not possible to UPSERT like in other RDBMSs?

John Magistr
  • 872
  • 3
  • 9
  • 22

1 Answers1

0

You can rewrite the query like this:

MERGE INTO TEST.TABLE
USING (select 'foo' as s) s ON COLUMN_1 = s
WHEN MATCHED THEN UPDATE SET COLUMN_2 = 'quux'
WHEN NOT MATCHED THEN INSERT (COLUMN_1, COLUMN_2) VALUES ('foo', 'bar')

This way you will eliminate DUAL table and keep correct join clause.

GriGrim
  • 2,891
  • 1
  • 19
  • 33