Delete by join when join is not exists-CodePudding

Trying to delete bonus balances in case if user is not exists. O2O relation, where bonusBalancesId is on users table

DELETE "balances" 
FROM "public"."bonus-balances" as "balances" 
LEFT JOIN "public"."users" as "users"  
ON "balances"."id"= "users"."bonusBalancesId" 
WHERE "users"."id" IS NULL;

Getting this error:

ERROR:  syntax error at or near ""balances""
LINE 1: DELETE "balances"

CodePudding user response：

That is not valid DELETE syntax. You need a co-related subquery using NOT EXISTS to do this:

delete from "bonus-balances"
where not exists (select *
                  from users u 
                  where u."bonusBalancesId" = "bonus-balances".id)