CODEWITHSUNDEEP
mysqlforeign-keyssql-delete
z3ple
z3ple

Reputation: 368

How to remove records from mysql table, but skip records that have constraints errors?

I try to remove records, but getting constraint error and query stops. Or I use 'delete ignore ...' but query stops too, only with warning. I can't set constraint check off, I just need remove records without constraints. Maybe mysql have some commands for this? I found nothing.

Upvotes: 1

Views: 834

Answers (2)

a1ex07
a1ex07

Reputation: 37364

The only way I can imagine is to write a query that returns rows from you master table which don't have details, and then join result of this query with master table , something like 

DELETE FROM table1 t
INNER JOIN 
(
   SELECT pk_column FROM table1 t1
   LEFT JOIN details1 d1 ON (d1.fk_column = t1.pk_column)
   // other LEFT JOIN[s] if you have more detail tables.
   WHERE d1.pk_column IS NULL
)a ON t.pk_column = a.pk_column

An alternative to join, which works pretty much the same is to add WHERE NOT EXIST to check

Upvotes: 3

cobaltduck
cobaltduck

Reputation: 1598

Add "on delete cascade" to the table/ constraint definition.

Upvotes: 0

Related Questions