On Sat, Aug 10, 2024 at 8:22 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
Why not use INSERT ... ON CONFLICT instead of MERGE?
>
> MERGE INTO tab1 AS target
> USING (VALUES ('5efd4c91-ef93-4477-840c-a723ae212d99', 123,
> '2024-08-09T11:33:49.402585600Z','2024-08-09T11:33:49.402585600Z')) AS
> source(id, mid,txn_timestamp, cre_ts)
> ON target.id <http://target.id> = source.id <http://source.id>
> WHEN MATCHED THEN
> UPDATE SET mid = source.mid
> WHEN NOT MATCHED THEN
> INSERT (id, mid, txn_timestamp, cre_ts)
> VALUES (source.id <http://source.id>,source.mid,
> source.txn_timestamp, source.cre_ts);
Actually , as per the business logic , we need to merge on a column which is not unique or having any unique index on it. It's the leading column of a composite unique key though. And in such scenarios the "INSERT.... ON CONFLICT" will give an error. So we are opting for a merge statement here, which will work fine with the column being having duplicate values in it.
