CODEWITHSUNDEEP
postgresqlpostgresql-9.4
Ratatouille
Ratatouille

Reputation: 1462

Delete a record if the associated Join entry does not exist

I'm trying to delete a record from a table if the parent table record does not exist.

The table in question are merchants and merchant_configurations

merchant_configurations has a foreign key(merchant_id) reference to merchant table primary key (id)

Here how the 2 tables looks

== merchant_configurations
  id           integer
  merchant_id  integer
  config_options  hstore

Merchant table

== merchants
  id integer
  name string

Now, select query to retrieve all those merchant_configurations record for whose merchant record is delete look like this

select merchant_configurations.id from merchant_configurations LEFT JOIN merchants ON merchant_configurations.merchant_id = merchants.id where merchants.id IS NULL

Now, I essentially want is to delete all those record but for some reason

DELETE merchants_configurations from select merchant_configurations.id from merchant_configurations LEFT JOIN merchants ON merchant_configurations.merchant_id = merchants.id where merchants.id IS NULL

Does not seem to work.

The only way I manage to get it done using WITH clause. 

WITH zombie_configurations AS (
   select merchant_configurations.id from merchant_configurations LEFT JOIN 
   merchants ON merchant_configurations.merchant_id = merchants.id where 
   merchants.id IS NULL 
)

DELETE from merchant_configurations where id IN (select id from zombie_configurations);

Now my question is:

Is it possible to delete the Record using normal way without having to do the WITH clause and stuff

Upvotes: 0

Views: 583

Answers (2)

Łukasz Kamiński
Łukasz Kamiński

Reputation: 5930

You can also use USING:

DELETE FROM merchant_configurations AS mc
USING merchant_configurations AS mc2
LEFT JOIN merchants ON mc2.merchant_id = merchants.id
WHERE mc2.id = mc.id AND merchants.id IS NULL

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246483

Use NOT EXISTS, it is simple and efficient:

SELECT FROM merchant_configurations mc
WHERE NOT EXISTS (SELECT 1
                  FROM merchants m
                  WHERE mc.merchant_id = m.id);

Upvotes: 1

Related Questions