Greenplum Delete statement not working

Question

We have a scenario in that, We need to compare staging table with Destination table.Finally, want to delete the record at destination table

We tried

DELETE  FROM DestTable
FROM    DestTable d
        LEFT JOIN StagingTable s ON d.ID = s.ID
WHERE   s.id IS NULL

Please advice on it. How do I create delete statement in this scenario?

Any help on it would be much appreciated?

Answer 1

This isn't supported in HAWQ. HAWQ only supports INSERT and TRUNCATE.

In Greenplum, be careful executing a lot of DELETE statements. You will want to run a VACUUM command either before or after running the DELETE. You will probably be better off using TRUNCATE and INSERT just like in HAWQ.

DELETE FROM DestTable d
USING StagingTable s 
WHERE d.ID = s.ID;