如何使用其他表中的外键删除主键的行？ [英] How do I delete row with primary key using foreign key from other table?

问题描述

我有一张名为议程的表格//翻译：乳品厂

包含以下行：

  idagenda //主键
 title 
 waar 
 organisatie 
 ... 
等
 ... 
  

我也有一个名为agendadatum的日记项目/事件的日期表

包含以下行：

  id //主键
 idagenda / / id from other table 
 van // from 
 tot // until 
 datum // date 
  

当tot字段是从Today开始的日期时，它将删除数据库中的行，但议程表中的行保持不变。他们不会被删除，因为我没有给他们打电话。

我的删除查询是这样的：

  DELETE FROM agendadatum WHERE tot<现在（）; 
  

如何删除'agenda'表中的行，您可以从一个查询中的多个表中删除：

$ b $

  DELETE议程。*，agendadatum。* 
从议程
加入agendadatum USING（idagenda）
 WHERE tot<现在（）; 
  

这将从议程和agendadatum中删除行，只有符合条件的行（与如果用 SELECT 替换 DELETE ），则返回查询。

I have a table called 'agenda' //translation: dairy

with the following rows:

idagenda // primary key
title
waar
organisatie
...
etc.
...

I also have a table for the date of an diary item/event called agendadatum

with the following rows:

id // primary key
idagenda // id from other table
van //from
tot // till
datum // date

When the field 'tot' is the date from Today it will delete the rows from the database, but the rows in the 'agenda' table remain untouched. They're not deleted, because I did not call them.

My delete query looks like this:

DELETE FROM agendadatum WHERE tot < NOW(); 

How can I also delete the rows from 'agenda' table, that have the same id then the foreign key in agendadatum?

解决方案

You can delete from multiple tables in one query:

DELETE agenda.*, agendadatum.*
FROM agenda
JOIN agendadatum USING (idagenda)
WHERE tot < NOW();

This will delete rows from both agenda and agendadatum, and only those rows matching the conditions (same rows as returned by the query if you replaced DELETE with SELECT).

这篇关于如何使用其他表中的外键删除主键的行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！