当联接不存在时，按联接删除

Question

尝试删除奖金余额，以防用户不存在。O2O关系，其中bonusBalancesId位于users表上

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

获取此错误：

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

Answer 1

这是无效的删除语法。要执行以下操作，需要使用NOT进行与共同相关的子查询：

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