Re: Re: new patch of MERGE (merge_204) & a question about duplicated ctid

On 2010-12-29 2:14 PM, Greg Smith wrote:
> MERGE INTO Stock t
>   USING (VALUES(10,100)) AS s(item_id,balance)
>   ON s.item_id=t.item_id
>   WHEN MATCHED THEN UPDATE SET balance=t.balance + s.balance
>   WHEN NOT MATCHED THEN INSERT VALUES(s.item_id,s.balance)
>   ;
>
> If you can suggest an alternate way to express this that works with the
> new patch, I might switch to that and retry.  I was never 100% sure this
> was the right way to write this, and I don't have another database with
> MERGE support here to try against.

As far as I can tell, this should work.  I played around with the patch 
and the problem seems to be the VALUES:

INTO Stock t
  USING (SELECT 30, 2000) AS s(item_id,balance)
  ON s.item_id=t.item_id
  WHEN MATCHED THEN UPDATE SET balance=t.balance + s.balance
  WHEN NOT MATCHED THEN INSERT VALUES(s.item_id,s.balance)
  ;
MERGE 1



Regards,
Marko Tiikkaja