如果关联的联接条目不存在，则删除记录 [英] 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.

有问题的表是商家和 merchant_configurations

merchant_configurations具有外键（ merchant_id ）对商家表<$ c $的引用c>主键（ id ）

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

这两个表的外观

== merchant_configurations
  id           integer
  merchant_id  integer
  config_options  hstore

商人桌

== 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

现在，我基本上是ant会删除所有这些记录，但出于某种原因

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 

似乎无效。

我设法使用WITH子句完成此操作的唯一方法。

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);

现在我的问题是：

是可以使用常规方法删除记录，而不必执行 WITH 子句和东西

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

推荐答案

您还可以使用使用：

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

这篇关于如果关联的联接条目不存在，则删除记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！