CODEWITHSUNDEEP
mysqlsqlreference
Vor
Vor

Reputation: 35109

delete rows, that doesn't have references in other tables;

I want to delete all rows in database, that doen't have a reference in another table:

this is ok:

mysql> DELETE FROM auth_user WHERE username = 'Sample41';
Query OK, 1 row affected (0.02 sec)

this is not ok:

mysql> DELETE FROM auth_user WHERE username = 'Sample98';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mydatabase`.`school_subjects`, CONSTRAINT `user_id_refs_id_9e8f6fd7` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`))

So my goal is to get a query that will delete all users (they will start with 'Sample') that doesn't have reference in other tables ( actually just one table 'school_subjects' )

thank you.,

UPDATE:

mysql> describe school_subjects;
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | int(11)      | NO   | PRI | NULL    | auto_increment |
| name             | varchar(500) | NO   |     | NULL    |                |
| user_id          | int(11)      | NO   | MUL | NULL    |                |
| created_by       | varchar(64)  | NO   |     | NULL    |                |
| created_time     | datetime     | NO   |     | NULL    |                |
| num_of_followers | int(11)      | NO   |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> describe auth_user;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| username     | varchar(30)  | NO   | UNI | NULL    |                |
| first_name   | varchar(30)  | NO   |     | NULL    |                |
| last_name    | varchar(30)  | NO   |     | NULL    |                |
| email        | varchar(75)  | NO   |     | NULL    |                |
| password     | varchar(128) | NO   |     | NULL    |                |
| is_staff     | tinyint(1)   | NO   |     | NULL    |                |
| is_active    | tinyint(1)   | NO   |     | NULL    |                |
| is_superuser | tinyint(1)   | NO   |     | NULL    |                |
| last_login   | datetime     | NO   |     | NULL    |                |
| date_joined  | datetime     | NO   |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

Upvotes: 2

Views: 6095

Answers (3)

Ulises
Ulises

Reputation: 13419

So my goal is to get a query that will delete all users (they will start with 'Sample') that doesn't have reference in other tables ( actually just one table 'school_subjects' )

You have a self-referencing table. If you want to delete all records you must include school_subjects and auth_user. Try:

DELETE FROM auth_user WHERE username not in (SELECT id FROM auth_user) 
and  username not in (SELECT user_id FROM school_subjects);

UPDATE: If you don't have a self-referencing constraint and only care about 'school_subjects' try the following query, note that it will delete all records that are not in conflict with constraints:

DELETE FROM auth_user WHERE username not in (SELECT user_id FROM school_subjects);

If you need to delete just a few you will need:

DELETE FROM auth_user 
WHERE username in ('SomeRecordToBeDeleted', 'one more') AND
username not in (SELECT user_id FROM school_subjects);

Upvotes: 7

Suresh Kamrushi
Suresh Kamrushi

Reputation: 16086

You can try this--

Delete from auth_user where username like 'Sample%' ANd auth_user.id NOT IN 
( select user_id from school_subjects where auth_user.id = school_subjects.user_id)

Upvotes: 1

PinnyM
PinnyM

Reputation: 35533

This will delete rows that don't have a matching 'user_id' in the 'school_subjects' table.

DELETE FROM auth_user 
WHERE id NOT IN (SELECT user_id FROM school_subjects);

Upvotes: 1

Related Questions