Question 188926 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – I am trying to delete only duplicate rows but my query is deleting all the records

Here is schema of table:-

INSERT INTO cars(id,model,brand,color,make)
VALUES
(1, 'Model S', 'Tesla', 'Blue', 2018),
(2, 'EQS', 'Mercedes-Benz', 'Black', 2022),
(3, 'iX', 'BMW', 'Red', 2022),
(4, 'Ioniq 5', 'Hyundai', 'White', 2021),
(5, 'Model S', 'Tesla', 'Silver', 2018),
(6, 'Ioniq 5', 'Hyundai', 'Green', 2021);

I am trying to delete only duplicate rows but my query is deleting all the records.

Below is the query i am using.

DELETE
FROM cars
WHERE id IN(
       SELECT id FROM(
              SELECT MAX(id)
              FROM cars
              GROUP BY model,brand)AS X);

I tried deleting only duplicate records but it is deleting all the records

Tags:

2

Answers


  1. 0

    Whoops… your query is actually treated as:

    DELETE
FROM cars
WHERE id IN (
    SELECT cars.id
    FROM -- rest does not matter
)

    That is because:

    • the max value column as no alias so it’ll be assigned an auto-generated alias
    • id is looked up starting from current level, then parent(s)

    The query will match all rows.

    To delete duplicates I personally use exists:

    SELECT * -- change to delete
FROM cars
WHERE EXISTS (
    SELECT *
    FROM cars AS x
    WHERE id < cars.id
    AND brand = cars.brand
    AND model = cars.model
)
    Login or Signup to reply.
  2. 0

    This will delete rows with duplicate model and brand. Rows with higher id will be deleted.

    DELETE FROM cars
WHERE id IN (
    SELECT
        id
    FROM (
        SELECT
            id,
            row_number() OVER w as rnum
        FROM cars
        WINDOW w AS (
            PARTITION BY model,brand
            ORDER BY id
        )

    ) t
WHERE t.rnum > 1);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search