Converting a SELECT result with a DISTINCT to a DELETE function in MySQL

Question

I'm learning MySQL add this this year. I'm trying to delete the results I got from this select statement:

select distinct *
from films a
inner join films b
on trim(upper(a.titel)) = trim(upper(b.titel))
where a.filmID > b.filmID

But their are a few problems: the database that we use is broken and contains many errors. For that reason testing out code will not always have the results we need. So we basically are running blind and just have the solve the questions without been able to test them properly.

Since our deadline is tomorrow we solved all questions except for this one. I wanted to solve this issue by doing this:

delete from films where (*) in (
 select distinct *
 from films a
 inner join films b
 on trim(upper(a.titel)) = trim(upper(b.titel))
 where a.filmID > b.filmID
);

And now I get this error:

My classmates think it's just an error in the database. But I suspect that's not the case.

Then I tried this:

delete from films where titel in (
 select distinct a.titel
 from films a
 inner join films b
 on trim(upper(a.titel)) = trim(upper(b.titel))
 where a.filmID > b.filmID
);

With this error:

Possible duplicate of [Delete with Join in MySQL](https://stackoverflow.com/questions/652770/delete-with-join-in-mysql) — PM 77-1, Dec 29 '17 at 16:01
That's why I'm a bit worried ^^ , most of the guys already send in their assignment... — Jens Ingels, Dec 29 '17 at 16:02
Please do not add answer material to questions, especially at the start, where it appears in reverse chronological order. Post them in the answer space below instead. — halfer, Jan 02 '18 at 13:19

score 2 · Answer 1 · answered Dec 29 '17 at 16:08

Consider the following:

DROP TABLE IF EXISTS films;

CREATE TABLE films
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,title VARCHAR(50) NOT NULL
);

INSERT INTO films (title) VALUES
('The Shining'),
('Jaws'),
('Casablanca'),
('Bladerunner'),
('The Shining'),
('Casablanca');


SELECT * 
  FROM films x 
  JOIN films y 
    ON y.title = x.title 
 WHERE y.id < x.id;
+----+-------------+----+-------------+
| id | title       | id | title       |
+----+-------------+----+-------------+
|  5 | The Shining |  1 | The Shining |
|  6 | Casablanca  |  3 | Casablanca  |
+----+-------------+----+-------------+

DELETE x FROM films x JOIN films y ON y.title = x.title WHERE y.id < x.id;
Query OK, 2 rows affected (0.01 sec)

SELECT * FROM films;
+----+-------------+
| id | title       |
+----+-------------+
|  1 | The Shining |
|  2 | Jaws        |
|  3 | Casablanca  |
|  4 | Bladerunner |
+----+-------------+

So something like: delete x from films x join films y on trim(upper(y.titel)) = trim(upper(x.titel)) where x.filmID > y.filmID; seems to work. Thx a lot :) — Jens Ingels, Dec 29 '17 at 16:18
Well, I'd run `UPDATE films SET titel = TRIM(titel)`. And you only need UPPER if you're using a case sensitive collation — Strawberry, Dec 29 '17 at 17:09

score 0 · Answer 2 · answered Jan 02 '18 at 13:16

0

(Posted a solution on behalf of the question author).

delete x
from films x
join films y
on trim(upper(y.titel)) = trim(upper(x.titel))
where x.filmID > y.filmID;

answered Jan 02 '18 at 13:16

halfer

19,824
17
99
186

Converting a SELECT result with a DISTINCT to a DELETE function in MySQL

2 Answers2