hey, guys, I came across a merge statement when I'm trying to convert stored procedures from Mysql to Postgres:
merge into db.ChargePeriod d
using (
select ba.ClientID
, ba.BillingAccountID
, bs.BillingScheduleID
, @CodeWithholdD as WithholdTypeID
from db.ClientPartyIDConfiguration cpc
join db.BillingAccount ba
on ba.Deleted = 0
and ba.ClientID = cpc.ClientID
and ba.PartyID = cpc.PartyID
and convert(date,getdate()) between ba.EffectiveDate and ba.ExpireDate
join db.BillingSchedule bs
on bs.Deleted = 0
and bs.ClientID = ba.ClientID
and bs.CoverageBeginDate >= ba.EffectiveDate
and bs.CoverageBeginDate <= ba.ExpireDate
where cpc.Deleted = 0
and cpc.ClientID = @ClientID
) s on d.Deleted = 0
and d.ClientID = s.ClientID
and d.BillingAccountID = s.BillingAccountID
and d.BillingScheduleID = s.BillingScheduleID
when matched
and isNull(d.WithholdTypeID,-1) <> isNull(s.WithholdTypeID,-1)
then update
set WithholdTypeID = s.WithholdTypeID
, UpdateUser = @UpdateUser
, UpdateDate = @UpdateDate
when not matched then insert (
ClientID
, BillingAccountID
, BillingScheduleID
, WithholdTypeID
, CreateUser
, CreateDate
, Deleted
, CancelDate
) values (
s.ClientID
, s.BillingAccountID
, s.BillingScheduleID
, s.WithholdTypeID
, @UpdateUser
, @UpdateDate
, 0
, '9999-12-31'
)
;
I saw some people use "with upsert as", but my pgAdmin version(1.8) doesn't support it. Anyone has any ideas how to do merge in postgres?
Thanks.
