How to write DELETE Statement with Inner Query in PostgreSQL?

The below query works fine in Oracle DB. The same query fails in Postgres DB, but the inner query works fine in Postgres DB.

DELETE FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY A.RESORT, A.RESV_NAME_ID ORDER BY ACTION_ID DESC) RNK
FROM STAGE_DETAILS A ) B 
WHERE B.RNK>1;

I am getting syntax error for this.

Error :- [Code: 0, SQL State: 42601] ERROR: syntax error at or near “(” Position: 13 [Script position: 3109 – 3110]

How to fix this ?

Thanks.

Answer

use following syntax

DELETE FROM STAGE_DETAILS A
USING (SELECT ACTION_ID,RESV_NAME_ID,ROW_NUMBER() OVER (PARTITION BY RESORT, RESV_NAME_ID ORDER BY ACTION_ID DESC) RNK
     FROM STAGE_DETAILS) B
WHERE B.RNK>1 AND B.ACTION_ID = A.ACTION_ID AND B.RESV_NAME_ID = A.RESV_NAME_ID;