How to use MERGE keyword in pl/sql?

Question

I am updating a table, but I keep getting follwing error

ERROR:  syntax error at or near "MERGE"
LINE 3: MERGE into

when i try to use a merge statement. I don't see anything obvious wrong with the syntax. can someone point out the obivous

MERGE into Table2 t2
using (select name, max(id) max_id from Table1 t1 group by name ) t1
on (t2.project_name=t1.name)
when matched then update set projectid=max_id where status='ongoing' ;

Table1

1  | alpha           | 2021 | 
2  | groundwork      | 2020 | 
3  | NETOS           | 2021 | 
5  | WebOPD          | 2019 | 

Table2 

id | name  | year | status     | project name      | projectID 
1  |  john | 2021  | ongoing    | alpha            | 1 
2  | linda | 2021  | completed  | NETOS            | 3 
3  | pat   | 2021  | WebOPD     | completed        | 5
4  | tom   | 2021  | ongoing    | alpha            | 1

version : PostgreSQL 13.6

Answer 1

The last line in your message says you use PostgreSQL. Tag you used (plsql) means Oracle. Which one is it, after all? I presume former, but - syntax you used is Oracle.

MERGE documentation for PostgreSQL says that

• INTO can't be used
• no parenthesis for ON clause
• WHERE clause can't be used

See if something like this helps:

MERGE Table2 t2
using (select t1.name, 
              max(t1.id) max_id 
       from Table1 t1 join table2 t2 on t2.project_name = t1.name
       where t2.status = 'ongoing'
       group by name 
      ) x 
on t2.project_name = x.name
when matched then update set 
  t2.projectid = x.max_id ;