Hello,

Out of curiosity, is there a simpler way to remove duplicate rows?

CREATE TABLE tmp_books AS SELECT DISTINCT * FROM books;
DROP TABLE books;
ALTER TABLE tmp_books RENAME TO books;
CREATE INDEX title_idx ON books(title);

Thank you.

Thank you.

The same Code lefts one duplicate in my table. Any suggestions?

The only sensible suggestion is that it is not in fact a duplicate.

You may need to furnish us with more information.

Of course if it is really a single duplicated row, you could just remove one of them directly.

The usual cause of confusion is that a value which looks like a number is actually a string. To unconfuse, select a quoting output mode.

Try returning typeof(field). If they are both strings, make sure there are no spaces or returns at the beginning or end of the string.

Whenever I'm dealing with output (string or numeric), I sometimes bookend the output with '-->' and '<--' so I can see if there are any leading or trailing spaces or other characters.

So in Perl I would say

print qq{-->$ThisString<--};

This shows me immediately any leading or trailing unwanted characters.

In the case of thousands of lines to verify / sanitize, I would output the whole result to a text file then use the appropriate tool to look for and make any changes (diff, sed, awk, perl, etc.).

while the other answers are great, I see nothing wrong with the above approach… it seems just as simple as any other way. Unless you have bazillions of rows and so, are resource-constrained, the above is as good a solution as any. And, you don't have to name all the columns individually.