当join不存在时，通过join删除

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

这是无效的DELETE语法。您需要一个使用NOT EXISTS的相关子查询来执行此操作:

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