0

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?

vs0731
  • 17
  • 1
  • 8

1 Answers1

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;
Jon Roberts
  • 2,068
  • 1
  • 9
  • 11
  • Thanks Jon. Trying on Greenplum only – vs0731 Aug 02 '17 at 15:48
  • Using clause is Inner join or Left Join.? – vs0731 Aug 02 '17 at 16:04
  • As mentioned above we need to write VACUUM as well. But fact is we cannot write in the SQL transaction. so, can we automate Vacuum after each run of the transaction at table level? – vs0731 Aug 02 '17 at 16:20
  • Can i try this : DELETE FROM DestTable d USING StagingTable s WHERE s.ID is null ; – vs0731 Aug 02 '17 at 17:02
  • It is an inner join. Delete only the records desttable where there is a match based on ID to the stagingtable. Your left join doesn't make sense and need to have the where condition for it to work correctly. You above example is wrong. – Jon Roberts Aug 02 '17 at 17:04