FB-3 Merge

[henry...@hotmail.com]

I have below Sql and it works

   MERGE INTO WHCOUNTDET WC  

USING
  (SELECT ITEMNO,COALESCE(SUM(TRS),0)TRS,       COALESCE(SUM(INCOME),0)INCOME,COALESCE(SUM(OUTGO),0)OUTGO
  FROM ITEMMOVEMENTS
  WHERE EXTRACT(YEAR FROM TDATE)=2024 and WHNO=1
  GROUP BY ITEMNO) IM
  ON (WC.ITEMNO = IM.ITEMNO)
  WHEN MATCHED THEN
    UPDATE SET
      WC.STOCK = (IM.TRS + IM.INCOME - IM.OUTGO);

I need to add a WHERE clause on WC Table  like

 WHERE WC.RNO=3

I Tried  

.....

UPDATE SET

   WC.STOCK = (IM.TRS + IM.INCOME - IM.OUTGO)  WHERE WC.RNO=3;

but i get Token unknown - line 9, column 62 WHERE (which indicates above bold WHERE)

where should i add WHERE WC.RNO=3  ?

Thank You

[Mark Rotteveel]

On 05/06/2024 14:19, henry...@hotmail.com wrote:
> I have below Sql and it works
>    MERGE INTO WHCOUNTDET WC
> USING
>   (SELECT ITEMNO,COALESCE(SUM(TRS),0)TRS,
>  COALESCE(SUM(INCOME),0)INCOME,COALESCE(SUM(OUTGO),0)OUTGO
>   FROM ITEMMOVEMENTS
>   WHERE EXTRACT(YEAR FROM TDATE)=2024 and WHNO=1
>   GROUP BY ITEMNO) IM
>   ON (WC.ITEMNO = IM.ITEMNO)
>   WHEN MATCHED THEN
>     UPDATE SET
>       WC.STOCK = (IM.TRS + IM.INCOME - IM.OUTGO);
>
> I need to add a WHERE clause on WC Table  like
>  WHERE WC.RNO=3
> I Tried
> .....
> UPDATE SET

>    WC.STOCK = (IM.TRS + IM.INCOME - IM.OUTGO) *WHERE WC.RNO=3*;

> but i get Token unknown - line 9, column 62 WHERE (which indicates above
> bold WHERE)
> where should i add WHERE WC.RNO=3  ?

You need to add the condition to the `ON` clause, or add the condition
to the `WHEN` clause (so `WHEN MATCHED AND <condition>`), but unless you
have a different `WHEN MATCHED` that should apply for that row, I would
add the condition in the `ON` clause.

Mark
--
Mark Rotteveel