MySQL 5.7 how to delete rows that have same values in one column but are not duplicate rows-CodePudding

Here are my data:

CREATE TABLE cats
(
    cat_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20),
    breed VARCHAR(20),
    age INT
);

INSERT INTO cats(name, breed, age)
VALUES
('otto', 'tabby', 10),
('jerry', 'mynx', 7),
('nina', 'stray', 7),
('gandolf', 'american shorthair', 13),
('dumbledore', 'wizard', 10);

mysql> select * from cats;
 -------- ------------ -------------------- ------ 
| cat_id | name       | breed              | age  |
 -------- ------------ -------------------- ------ 
|      1 | otto       | tabby              |   10 |
|      2 | jerry      | mynx               |    7 |
|      3 | nina       | stray              |    7 |
|      4 | gandolf    | american shorthair |   13 |
|      5 | dumbledore | wizard             |   10 |
 -------- ------------ -------------------- ------

What I want to do is delete all the rows where the ages are the same in more than one row. In the example, after deletion, the only row I would have left is the row with cat_id = 4.

| cat_id | name    | breed              | age  |
 -------- --------- -------------------- ------ 
|      4 | gandolf | american shorthair |   13 |
 -------- --------- -------------------- ------

I have tried:

DELETE FROM cats WHERE age IN (SELECT age FROM cats GROUP BY age HAVING COUNT(*) > 1)

but that gives me an error: ERROR 1093 (HY000): You can't specify target table 'cats' for update in FROM clause.

I've tried using a JOIN, too, but that ends up deleting all the rows.

Any help is greatly appreciated!

I can do this in MySQL v 8.0 but I can't figure out how to do it in 5.7.

CodePudding user response：

MySql is a little fussy, try nesting your criteria in its own subquery:

 DELETE FROM cats 
 WHERE age IN (
  SELECT age FROM (
    SELECT age FROM cats 
    GROUP BY age 
    HAVING COUNT(*) > 1
  )a
);

Demo Fiddle

CodePudding user response：

Well, about 3 minutes after I posted this question, I figured out the answer. I was not using the right JOIN criteria.

Here is the solution I came up with - any critique is greatly appreciated!

DELETE c1 
FROM cats c1
JOIN cats c2
ON c1.age = c2.age
AND c1.name <> c2.name;

From the command line client:

mysql> DELETE c1
    -> FROM cats c1
    -> JOIN cats c2
    -> ON c1.age = c2.age
    -> AND c1.name <> c2.name;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from cats;
 -------- --------- -------------------- ------ 
| cat_id | name    | breed              | age  |
 -------- --------- -------------------- ------ 
|      4 | gandolf | american shorthair |   13 |
 -------- --------- -------------------- ------ 
1 row in set (0.00 sec)

CodePudding user response：

Looking up this error i found Yehor answer, he provided a really good solution for the problem.

If you do this:

DELETE FROM cats 
WHERE age 
IN (SELECT age FROM cats GROUP BY age HAVING COUNT(*) > 1)

you are going to get an error.

But if you wrap the condition in one more select:

DELETE FROM cats 
WHERE age IN (
  SELECT age FROM (
    SELECT age FROM cats GROUP BY age HAVING COUNT(*) > 1
  ) AS a
)

You probably will receive this error:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

Just disable safe mode and it would do the right thing!!

Explanation: The query optimizer does a derived merge optimization for the first query (which causes it to fail with the error), but the second query doesn't qualify for the derived merge optimization. Hence the optimizer is forced to execute the subquery first.