PostgreSQL merge query for update stock information - syntax error at or near "MERGE"-CodePudding

I have 2 tables, main_table has product_id, product_stock and product_name columns.

update_table has product_id, product_stock columns.

I want to update product_stock for main_table.

I wrote this query:

MERGE INTO main_table MT
USING update_table UT
ON MT.product_id = UT.product_id
WHEN MATCHED THEN
  UPDATE SET MT.product_stock = UT.product_stock ;

But this query returning with error

ERROR:  syntax error at or near "MERGE"

Why my query is not working and how can i make this work ?

CodePudding user response：

As others have indicated Postgres does not (yet) have the merge statement. However, you do not need it. Your statement will update the main_table for the product_id from the update_table that exist in main_table, however if the product_id in update_table does not exist in main_table, the statement bypasses the update and continues. This is because an attempt to update a non-existing row is not an error. Postgres does the same thing with update ... from ... So: (see demo)

update main_table mt  
   set product_stock = ut.product_stock
  from update_table ut
 where ut.product_id = mt.product_id;

The downside being you get no notification of the updates that did not happen. But then Merge does not give a notice either.