StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhy do I need to run this script twice to delete all duplicates?
text
Body
copied!<p>What I do is create a temp table called Rent2 to deposit all duplicate modules my clients have registered, then I do a delete inner join statement before deleting the temp table.</p> <p>But there is always a left over duplicate after this is done. What am I doing wrong here?</p> <pre><code>CREATE TABLE IF NOT EXISTS `tblRent2` ( `IdRent` INT(11) NOT NULL) ENGINE = InnoDB; INSERT INTO tblRent2 (SELECT IdRent FROM tblRent WHERE IdModule = 1 GROUP BY IdClient HAVING COUNT(IdModule) > 1); INSERT INTO tblRent2 (SELECT IdRent FROM tblRent WHERE IdModule = 2 GROUP BY IdClient HAVING COUNT(IdModule) > 1); INSERT INTO tblRent2 (SELECT IdRent FROM tblRent WHERE IdModule = 3 GROUP BY IdClient HAVING COUNT(IdModule) > 1); INSERT INTO tblRent2 (SELECT IdRent FROM tblRent WHERE IdModule = 4 GROUP BY IdClient HAVING COUNT(IdModule) > 1); INSERT INTO tblRent2 (SELECT IdRent FROM tblRent WHERE IdModule = 5 GROUP BY IdClient HAVING COUNT(IdModule) > 1); INSERT INTO tblRent2 (SELECT IdRent FROM tblRent WHERE IdModule = 6 GROUP BY IdClient HAVING COUNT(IdModule) > 1); INSERT INTO tblRent2 (SELECT IdRent FROM tblRent WHERE IdModule = 999 GROUP BY IdClient HAVING COUNT(IdModule) > 1); DELETE r.* FROM tblRent r INNER JOIN tblRent2 r2 ON r.idRent = r2.idRent; SELECT * FROM tblRent2; DROP TABLE tblRent2; </code></pre> <p>The Original table looks like this:</p> <pre><code>IdRent | IDClient | IdModule 1 | 30 | 999 2 | 30 | 999 3 | 31 | 1 4 | 31 | 1 </code></pre> <p>Those are all primary keys.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload