Postgresql: deleting duplicate records by CompanyID and CustomerID

Question

I can't get the following query to work. It will work if I remove the last line of the query. Any suggestions are greatly appreciated.

DELETE FROM "ProjectMaster"
USING "ProjectMaster" ua2
WHERE "ProjectMaster"."EngagementName" = ua2."EngagementName" 
AND "ProjectMaster"."ProjectMasterID" < ua2."ProjectMasterID"
AND "CompanyID" = 490 and "ClientID" = 11125

Answer 1

When I run your query in a fiddle, it gives me :

error: relation "ProjectMaster" does not exist

Here are a few changes that can be done to avoid this error, without altering the logic (that you did not actually explain) :

• alias the table that you insert to (ua1)
• prefix the fields in the last line with the table they belong to (ua1 or ua2 ?)
• get rid of these noisy quotes

This runs without syntax error :

DELETE FROM ProjectMaster ua1
USING ProjectMaster ua2
WHERE 
    ua1.EngagementName = ua2.EngagementName
    AND ua1.ClientID = ua2.ClientID
    AND ua1.ProjectMasterID < ua2.ProjectMasterID
    AND ua1.CompanyID = 490 and ua1.ClientID = 11125;