How to remove unused rows from a database table using SQL?

Question

I am trying to remove unused rows from a table. This is a simplified example of my problem:

There are 2 tables:

user table: user_id user_name -------------------- 1 Mike 3 Carol 8 Eric address table: user_id address ----------------------- 1 [e-mail] 3 [e-mail] 10 [e-mail] 3 [e-mail] 

I want to remove unused addresses from the address table. If user_id addresses exist in the user table, the address is not used. There is one unused address in the example table: [e-mail].

I am new to SQL and my solution was ugly:

 DELETE FROM address WHERE NOT EXISTS ( SELECT * FROM user WHERE address.user_id = user.user_id ); 

There must be a better way to do this. What is the best way to do this?

Is used

sqlite.

Answer 1

Do it like this:

 DELETE FROM address WHERE user_id NOT IN (SELECT user_id FROM user); 

Answer 2

In your case, you should set user_id as the primary key in user and as a foreign key in address . Thus, if you delete one of your users in user , you can set the ON DELETE CASCADE parameter to automatically delete related lines in the address.

When creating the table, you only need to do something like this:

 CREATE TABLE user ( user_id INTEGER, user_name VARCHAR(30), PRIMARY KEY (user_id) ); CREATE TABLE address ( user_id INTEGER, address VARCHAR(30), PRIMARY KEY (user_id, address), FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE ); 

Basically, each user has a unique user_id . If you delete one of these users, the corresponding addresses in address will also be deleted, because the user.user_id address.user_id element "points to" has been deleted.

If you want to remove only those unused addresses, your solution should work fine.