sql query to remove duplicates doesn't work

I have the following mysql query:

select id, titulo_filme,nota_imdb,tempo_duracao, count(*) from imdb group by titulo_filme,nota_imdb,tempo_duracao having count(*) > 1

It works as expected and returns all duplicates. But when I remove the query to delete it is treating the string field as if it were an integer, and this generates a syntax error.

See the generated query to delete:

DELETE FROM `imdb` WHERE `imdb`.`id` = 77280 AND `imdb`.`titulo_filme` = \'24 Little Hours\' AND `imdb`.`nota_imdb` = 3,3 AND `imdb`.`tempo_duracao` = 1 hora 24 minutos LIMIT 1

The fields nota_imdb and tempo_duração are of the "text" type, but it insists on treating it as an integer and therefore I cannot delete it. It seems to me that this is happening because it starts with a number. But I already changed it to varchar and it doesn't work either.

Can anyone tell me why this is happening?

LaryAI

2 years ago

Level 58

The issue is with the generated delete query where the value for nota_imdb is 3,3 instead of 3.3 and the value for tempo_duracao is not enclosed in quotes. To fix this, you can modify the original query to replace the comma with a dot for nota_imdb and enclose tempo_duracao in quotes. Here's the modified query:

SELECT id, titulo_filme, nota_imdb, tempo_duracao, COUNT(*) 
FROM imdb 
GROUP BY titulo_filme, nota_imdb, tempo_duracao 
HAVING COUNT(*) > 1;

And here's the modified delete query:

DELETE FROM `imdb` 
WHERE `imdb`.`id` = 77280 
AND `imdb`.`titulo_filme` = '24 Little Hours' 
AND `imdb`.`nota_imdb` = '3.3' 
AND `imdb`.`tempo_duracao` = '1 hora 24 minutos' 
LIMIT 1;

Note that I've enclosed the values for nota_imdb and tempo_duracao in quotes to ensure that they are treated as strings.

mvnobrega

2 years ago

Level 3

@LaryAI

But the titles of the column titulo_filme are not enclosed in quotes and the query places automatic quotes, as it is a column of type text

click

2 years ago

Level 35

"See the generated query to delete:"

Who generated that query and how?

mvnobrega

2 years ago

Level 3

@click

The query generated to delete is this:

DELETE FROM `imdb` WHERE `imdb`.`id` = 77280 AND `imdb`.`titulo_filme` = \'24 Little Hours\' AND `imdb`.`nota_imdb` = 3,3 AND `imdb`.`tempo_duracao` = 1 hora 24 minutos LIMIT 1

And it was generated here, when I click remove:

https://prnt.sc/sG8AfBjSCA9x

then I get this error: https://prnt.sc/zG9PuqHIKuL4

click

2 years ago

Level 35

@mvnobrega That is a query generated by PHPMyAdmin, not any of your code? Unless I don't understand your question this has nothing to do with PHP or Laravel?

Why don't you just do DELETE FROM imdb WHERE id = 77280? That should be enough as the id is unique

mvnobrega

2 years ago

Level 3

@click

Because I want to remove all duplicates, there are more than a thousand duplicate records

Snapey

2 years ago

Level 122

You only need to specify the primary key to delete a record?

You are saying you have multiple records with the same id field?

Please or to participate in this conversation.