CODEWITHSUNDEEP
mysqlsql
Szántó Zoltán
Szántó Zoltán

Reputation: 981

Remove unnecessary rows from a relational table

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?

Upvotes: 1

Views: 75

Answers (2)

Filipe Silva
Filipe Silva

Reputation: 21657

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

Upvotes: 1

M.Ali
M.Ali

Reputation: 69524

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

Upvotes: 0

Related Questions