CODEWITHSUNDEEP
sqlpostgresql
Artem
Artem

Reputation: 397

Delete by join when join is not exists

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"

Upvotes: 0

Views: 43

Answers (1)

user330315
user330315

Reputation:

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)

Upvotes: 1

Related Questions