Building SQL Query To Remove PowerDNS Records

Question

I am trying to build a SQL query that removes domains from a PowerDNS database. The tables are called, domains and domains_status. I want to delete both records only if domains_status where the columns name domain_regStatus = 0. The ID value is the same for both domains and domain_status.

Here is an example of how the database looks,

pdns_domain_status pdns_domains

[Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/questions/285551/why-should-i-not-upload-images-of-code-data-errors-when-asking-a-question) — Akina, Aug 17 '22 at 13:42

score 0 · Answer 1 · answered Aug 17 '22 at 13:50

Use DELETE with a join between the tables

DELETE d, ds
FROM domains AS d
JOIN domain_status AS ds on d.id = ds.domain_id
WHERE ds.domain_regStatus = 0

If domain_status.domain_id is declared as a foreign key with ON DELETE CASCADE, you only need to delete from the domains table, it will automatically delete from domain_status. So change to:

DELETE d
FROM domains AS d
JOIN domain_status AS ds on d.id = ds.domain_id
WHERE ds.domain_regStatus = 0

Building SQL Query To Remove PowerDNS Records

1 Answers1