Delete user from MySQL table if no other users exist with similar traits

Question

I have the following table called tbl_test in a MySQL database :

+------+------------+------------+
| name | fav_color  | dob        |
+======+============+============+
| jane | blue       | 1996-07-07 |
+------+------------+------------+
| jane | pink       | 1996-07-07 |
+------+------------+------------+
| jane | pink       | 2016-07-07 |
+------+------------+------------+
| joe  | pink       | 2001-07-07 |
+------+------------+------------+
| john | red        | 1997-07-07 |
+------+------------+------------+

In English, here's what I want to accomplish:

delete all users with dob < 2001-01-01 but keep a user if there exists another user with the same name AND fav_color whose dob >= 2001-01-01.

So in this example, the jane whose favorite color is blue and john would be deleted.

So far, I've tried:

DELETE FROM tbl_test
WHERE dob < '2000-01-01' AND name, fav_color NOT IN
(SELECT T.name, T.fav_color FROM
(SELECT name, fav_color FROM tbl_test WHERE dob >= '2000-01-01') T);

But MySQL throws a syntax error when I enter this command. Any tips for how I can achieve this?

Gordon Linoff · Accepted Answer · 2017-07-07 17:08:42Z

1

MySQL doesn't let you refer to the table being deleted in the rest of the statement. So:

DELETE t
    FROM tbl_test t LEFT JOIN
         (SELECT t2.name, t2.fav_color
          FROM tbl_test t2
          WHERE dob >= '2000-01-01'
         ) tt
         ON t.name = tt.name AND t.fav_color = tt.fav_color
    WHERE t.dob < '2000-01-01' AND tt.name IS NULL;

Your method is very reasonable. And, you do have some other syntax errors (such as the first argument to NOT IN should have parentheses). Even if you fix those, though, you'll have a problem with the query.

answered Jul 7, 2017 at 17:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

RMT · Accepted Answer · 2017-07-07 17:08:52Z

0

I am not sure if this is exactly what you want, but your use of IN is not correct. I think you want to use EXIST (or in this case NOT EXIST)

DELETE FROM tbl_test
WHERE dob < '2000-01-01' 
AND NOT EXIST(
    SELECT null FROM tbl_test as tt
    WHERE tt.name = tbl_test.name
        AND tt.fav_color = tbl_test.fav_color
        AND dob >= '2000-01-01'
)

answered Jul 7, 2017 at 17:08

RMT

7,0904 gold badges28 silver badges39 bronze badges

Comments

Bill Karwin · Accepted Answer · 2017-07-07 17:16:45Z

0

You can do this without subqueries by using an exclusion join.

Try to join a user "t1" to another user "t2" with the criteria you describe. If none is found, the OUTER JOIN will make all columns NULL for t2. Where this happens, delete t1.

DELETE t1
FROM tbl_test AS t1
LEFT OUTER JOIN tbl_test AS t2
  ON t1.name = t2.name AND t1.fav_color = t2.fav_color AND t2.dob >= '2000-01-01'
WHERE t1.dob < '2000-01-01'
  AND t2.name IS NULL;

answered Jul 7, 2017 at 17:16

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Collectives™ on Stack Overflow

Delete user from MySQL table if no other users exist with similar traits

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related