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

Answer 1

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