Remove unnecessary rows from a relational table

Question

I have a relational database (mysql) with two tables: Cities and Events .

Cities table:

   - city_id
   - city_name
   - city_coord

Events table

   - event_id
   - event_name
   - event_descr
   - city_id

In the Cities table I have 1500+ rows, and nearly 1300 rows aren't linked to the events table. How can I delete these rows?

score 1 · Accepted Answer · edited May 23 '17 at 12:23

1

You can do:

DELETE FROM cities
WHERE city_id NOT IN (SELECT city_id FROM events)

As mentioned in the comments, if city_id can be null in events, this will not work. See this answer on why that is

edited May 23 '17 at 12:23

Community

answered Dec 01 '13 at 14:56

Filipe Silva

score 0 · Answer 2 · answered Dec 01 '13 at 14:56

0

DELETE FROM Cities
WHERE city_id NOT IN (SELECT city_id 
                       FROM Events)

answered Dec 01 '13 at 14:56

M.Ali

2 Answers2