CODEWITHSUNDEEP
mergesap-aseupsertsybase-ase15
user2800089
user2800089

Reputation: 2401

sybase merge query with where clause in insert or skip the insert on condition

I have a table with account_number, amount , last_updated_time.

I wrote a merge query as below

MERGE INTO account AS target
USING (SELECT ?,?,?)
AS SRC(account_number, amount , last_updated_time)
ON target.account_number = src.account_number AND src.last_updated_time > target.last_updated_time
WHEN MATCHED THEN
UPDATE SET target.amount=src.amount, target.last_updated_time = src.last_updated_time
WHEN NOT MATCHED THEN
INSERT(account_number, amount , last_updated_time)
VALUES(src.account_number, src.amount , src.last_updated_time)

Above query is failing because of unique constraint exception as account_number is primary key. I believe this violation is coming when any old records having lower timestamp are coming then it's going in insert clause and there it's failing.

How can I modify my merge query so that old or duplicate records are skipped and only new records are inserted ?

Upvotes: 2

Views: 76

Answers (1)

markp-fuso
markp-fuso

Reputation: 35256

ASE's merge statement allows secondary conditionals to be defined in the when [not] matched clause.

In this case we want to first match on account_number and if matched then we test last_updated_time:

merge into account as TGT
using (select ?, ?, ?) AS SRC (account_number, amount, last_updated_time)

on    SRC.account_number    = TGT.account_number
--and SRC.last_updated_time > TGT.last_updated_time            -- remove from 'on' clause

when  matched
and   SRC.last_updated_time > TGT.last_updated_time            -- add to 'when matched' clause

then
      update set TGT.amount            = SRC.amount,
                 TGT.last_updated_time = SRC.last_updated_time
when  not matched
then
      insert (    account_number,     amount,     last_updated_time)
      values (SRC.account_number, SRC.amount, SRC.last_updated_time)

Taking for a test drive ...

Create table:

create table account
(account_number         int             NOT NULL
,amount                 money
,last_updated_time      datetime
,constraint pk1 primary key (account_number)
)
go

select * from account
go

 account_number amount last_updated_time 
 -------------- ------ -----------------

NOTE: while OP appears to be using the merge command in a prepared statement, I'm going to use static values for demonstration purposes

merge insert:

merge into account as TGT
using (select 100, $500, '20240520') AS SRC (account_number, amount, last_updated_time)
on    SRC.account_number    = TGT.account_number
when  matched
and   SRC.last_updated_time > TGT.last_updated_time
then
      update set TGT.amount            = SRC.amount,
                 TGT.last_updated_time = SRC.last_updated_time
when  not matched
then
      insert (    account_number,     amount,     last_updated_time)
      values (SRC.account_number, SRC.amount, SRC.last_updated_time)
go

select * from account
go

 account_number amount last_updated_time   
 -------------- ------ ------------------- 
            100 500.00 May 20 2024 12:00AM

merge update (skipped):

merge into account as TGT
using (select 100, $300, '20240520') AS SRC (account_number, amount, last_updated_time)
on    SRC.account_number    = TGT.account_number
when  matched
and   SRC.last_updated_time > TGT.last_updated_time
then
      update set TGT.amount            = SRC.amount,
                 TGT.last_updated_time = SRC.last_updated_time
when  not matched
then
      insert (    account_number,     amount,     last_updated_time)
      values (SRC.account_number, SRC.amount, SRC.last_updated_time)
go

select * from account
go

 account_number amount last_updated_time   
 -------------- ------ ------------------- 
            100 500.00 May 20 2024 12:00AM     -- no change since the 'when matched'
                                               -- conditional on 'last_updated_time' fails

merge update:

merge into account as TGT
using (select 100, $300, '20240720') AS SRC (account_number, amount, last_updated_time)
on    SRC.account_number    = TGT.account_number
when  matched
and   SRC.last_updated_time > TGT.last_updated_time
then
      update set TGT.amount            = SRC.amount,
                 TGT.last_updated_time = SRC.last_updated_time
when  not matched
then
      insert (    account_number,     amount,     last_updated_time)
      values (SRC.account_number, SRC.amount, SRC.last_updated_time)
go

select * from account
go

 account_number amount last_updated_time   
 -------------- ------ ------------------- 
            100 300.00 Jul 20 2024 12:00AM     -- update performed since 'when matched'
                                               -- conditional on 'last_updated_time' succeeds

NOTE: tested with ASE 16.0 SP04 PL04

Upvotes: 1

Related Questions