CODEWITHSUNDEEP
phpmysql
EnexoOnoma
EnexoOnoma

Reputation: 8836

MySQL query that deletes all rows from A table where the id is not found on B table?

In my mySQL database, under the table audience there is a column called unique_hash where it has unique identifiers for each of my visitors.

In the table behaviour there is a column unique_hash and page. A unique_hash can have many rows, contrary to the audience table. However, the unique_hash found on behaviour are from the visitors that already saved them into the audience table.

Unfortunately for some maintenace reasons, I lost some of my data, and the result is that the distinct number of unique_hash in table behaviour are more than the unique hash of audience, the time that this should be equal.

What is the mysql query that will delete all the rows in the behaviour table where the unique_hash are not found on the audience table?

Upvotes: 0

Views: 35

Answers (1)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You can use NOT IN like the following:

DELETE FROM behaviour 
WHERE NOT unique_hash IN (
    SELECT DISTINCT unique_hash FROM audience
)

A second solution would be to use NOT EXISTS:

DELETE FROM behaviour
WHERE NOT EXISTS (
    SELECT * FROM audience 
    WHERE audience.unique_hash = behaviour.unique_hash
)

Upvotes: 2

Related Questions