Search code examples
mysqlsqlsql-delete

How to delete from one table if it do not exist in the another

I have a MySQL database where i have two tables. One table "url" and one called "stats".

in the table "url" the column "id" has the exact same value as the column "page_title" which is in the table "stats".

What i want to do is to check every entry from the table "stats.page_title" against "url.id". if the value from "stats.page_title" cant be found in "url.id" the entry from "stats.page_title" should be deleted.

How could i to this ?

Solution

  • With NOT EXISTS:

    delete from stats
where not exists (select 1 from url where url.id = stats.page_title)

    or with a LEFT JOIN:

    delete s
from stats s
left join url u on u.id = s.page_title
where u.id is null

    The condition where u.id is null will delete only the unmatched rows.