Mysql, delete unexisting entries

Question

Let's imagine he have two tables

--
-- Table structure for table `user`
--

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `phoneID` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


--
-- Table structure for table `phone`
--

CREATE TABLE IF NOT EXISTS `phone` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userID` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

So now I need create such query - I need delete all phone-entries from phone table where userID not exist (means user was deleted from user table and there is no such userID) using only one query.

My thoughts: we can use innerJoin to select all equal entries and then somehow we need delete others from phone table.

Please help with it.

WBR

Show us what you've got so far and we'll be happy to help you. — Remko, Aug 08 '13 at 13:07
possible duplicate of [Delete sql rows where IDs do not have a match in another table](http://stackoverflow.com/questions/3384127/delete-sql-rows-where-ids-do-not-have-a-match-in-another-table) — Remko, Aug 08 '13 at 13:09
Yes, RMK thanks, it's duplicate. Close please. And thanks again. — user1954544, Aug 08 '13 at 13:12
Your table structure is rather unusual. Are you sure you nee to have a userID column in the phone table? Also, you're createing an unnecesary circular reference. If a user can only have one phone, leave phoneId in user and remove userID from phone. If a phone can only belong to one user, then remove phoneID from user and leave userID in phone. If a user can have more than one phone and a phone can be owned by more than one user, remove both foreign keys and add a new table with two columns, phoneID and userID that you'll use to join phones and users. — Juan Pablo Califano, Aug 08 '13 at 13:14
By the way, to me a phone table doesn't make sense unless a user can have more multiple phones. — Juan Pablo Califano, Aug 08 '13 at 13:15
It was a simple quick example, i think all readers understood what i was asking about. — user1954544, Aug 08 '13 at 13:19
Thank you user1954544; now I know that I am not alone, and there's at least one other person that gives a table a name that is singular (rather than plural), and naming what one row represents. — spencer7593, Aug 08 '13 at 16:54

spencer7593 · Answer 1 · 2013-08-08T16:51:34.763

You're on the right track. But what you need is a query that does an "anti-join", to get all rows from phone that don't have a matching row in user.

Write the query first:

SELECT p.*
  FROM `phone` p 
  LEFT
  JOIN `user` u 
    ON u.id = p.userID
 WHERE u.id IS NULL

Notice that this does a left join, so it's saying "get me ALL rows from phone, along with any matching rows from user". The trick to the anti-join is the WHERE clause, which says, "eliminate all the rows where we found a match". So what we are left with is rows from phone that didn't have a matching row in user.

After you verify this is the set of rows you want to delete, and then just change the SELECT keyword to DELETE:

-- SELECT p.*
DELETE p.*
  FROM `phone` p 
  LEFT
  JOIN `user` u 
    ON u.id = p.userID
 WHERE u.id IS NULL

http://dev.mysql.com/doc/refman/5.5/en/delete.html

BTW: It's a bit unusual to have foreign key columns in each table referring to the other. There's no "rule" against it, but it's usually not required to satisfy a requirement that can be met by having a foreign key in just one table or the other. There are some corner cases where we'd need foreign keys pointing in both directions, but this a rare exception.

score 0 · Accepted Answer · edited Aug 08 '13 at 17:29

0

delete phone where userid not in (select user_id from user);

BTW, PhoneID should not be a field in the user table.

edited Aug 08 '13 at 17:29

Praveen Prasannan

7,093
10
50
70

answered Aug 08 '13 at 13:09

basdwarf

422
1
3
8

Mysql, delete unexisting entries

2 Answers2