PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL Error [42601]: ERROR: syntax error at or near "where" Position: 234

Merge /*+ parallel (16)*/ into psx_cluster_audit_t_1 a 
using psx_cluster_cross_ref_t_1  b 
on (
        a.psx_id=b.RECORD_ID
    and a.PSX_BATCH_ID=b.PSX_BATCH_ID
    and b.psx_batch_id='2411121519014999'
)
when matched then update set mpc_new=b.mpc where a.mpc_new is null

The above query giving:

SQL Error [42601]: ERROR: syntax error at or near "where"
Position: 234 error in postgresql

I tried with other clauses like ON

Tags:

2

Answers


  1. 0 
    MERGE /*+ parallel (16)*/ INTO psx_cluster_audit_t_1 a 
USING psx_cluster_cross_ref_t_1 b 
ON (
    a.psx_id = b.RECORD_ID
    AND a.PSX_BATCH_ID = b.PSX_BATCH_ID
    AND b.psx_batch_id = '2411121519014999'
)
WHEN MATCHED THEN 
    UPDATE SET a.mpc_new = b.mpc 
    WHERE a.mpc_new IS NULL;

    try this one

    Login or Signup to reply.
  2. 0

    You have a couple syntax errors and a totally superfluous/misleading comment. So:

    • Postgres syntax does not accept a where clause within the when matched phase. Move it into the on phase.
    • The update within the when matched phase must refer to only the table named in the into clause; therefore do not alias the column names.
    • Finally this looks like it was originally an Oracle statement. The comment ( /*+ … */) is how Oracle indicates a hint to its optimizer. In Postgres it’s just does nothing and does nothing except perhaps cause confusion.

    Taking all this your query becomes:

    MERGE  INTO psx_cluster_audit_t_1 a 
USING psx_cluster_cross_ref_t_1 b 
ON (
    a.psx_id = b.RECORD_ID
    AND a.PSX_BATCH_ID = b.PSX_BATCH_ID
    AND b.psx_batch_id = '2411121519014999'
    and a.mpc_new IS NULL      -- where clause from WHEN MATCHED Phased
)
WHEN MATCHED THEN 
    UPDATE SET mpc_new = b.mpc;

    NOTE Not tested. No table description (ddl) nor test data supplied.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search