User1 User1 - 2 months ago 10
SQL Question

How to delete unused rows from DB table by using SQL?

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

There are 2 table:

user table:

user_id user_name
--------------------
1 Mike
3 Carol
8 Eric


address table:

user_id address
-----------------------
1 mike@abc.com
3 carol@yyy.com
10 bob@xxx.com
3 carol@xxx.com


I want to delete unused addresses from the address table.
If user_id of an address does exists in user table, then the address is unused.
There is one unused address in the example table: bob@example.com.

I am new with 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 better way to do it.
What is the best way to do it?

sqlite is used.

Answer

Do it like this:

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