Please help - duplicated rows

[karimali831]

Hi all,

 

This is a rare occurrence I get duplicated rows but it can happen for some reason which leads to big problems.

Not sure how they are duplicating so I would rather just get the other row(s) deleted.

 

cupID and matchno columns should be always be unique, when I set this in phpmyadmin it will be return as there is a duplicate.

So I am looking for a script to delete duplicates then I can set as unique.

 

Here is example: the row(s) to be deleted is the latest, so the row not to be deleted is the first insert.

 

Anyone help me with this please?

 

Many thanks.

[snowdog]

how is the data input into the database? Is someone inputting the data and hitting a submit button?

[requinix]

So you want to keep the rows with the earliest "date" field? How about

DELETE FROM table AS t1
JOIN (
SELECT cupID, matchno, MIN(date) AS mindate
FROM table
GROUP BY cupID, matchno
HAVING COUNT(1) > 1
) AS t2 ON t1.cupID = t2.cupID AND t1.matchno = t2.matchno AND t1.date > t2.mindate

 

But you really, really should figure out where the duplicates are coming from. It's a problem. Problems are bad.

[karimali831]

Thanks both,

 

One of the ways it inserts is this code:-

 

$clans=safe_query("SELECT clanID FROM ".PREFIX."cup_clans WHERE cupID='$cupID' && checkin='1'");
while($dv=mysql_fetch_array($clans)) {
if($n < 1)
$n=1;
$clan[$n] = $dv['clanID'];
$n++;
}
$count = count($clan);

while($count < $max){
$count++;
$clan[$count] = 2147483647;
}

shuffle($clan);

$i2=0;
for ($i = 1; $i <= $max; $i++) {
if($clan[$i2] || $clan[$i2+1])
safe_query("INSERT INTO ".PREFIX."cup_matches (cupID, ladID, matchno, date, clan1, clan2, score1, score2, server, hltv, report, comment, type) VALUES ('$cupID', '0', '$i', '".time()."', '".$clan[$i2]."', '".$clan[$i2+1]."', '', '', '', '', '', '2', 'cup')");
$i2 += 2;
}

 

And when I try running the below query I receive #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS t1 JOIN ( SELECT cupID, matchno, MIN(date) AS mindate FROM ws' at line 1

 

I can't understand this query sorry.

 

DELETE FROM ws_B3T_cup_matches AS t1

JOIN (

SELECT cupID, matchno, MIN(date) AS mindate

FROM ws_B3T_cup_matches

GROUP BY cupID, matchno

HAVING COUNT(1) > 1

) AS t2 ON t1.cupID = t2.cupID AND t1.matchno = t2.matchno AND t1.date > t2.mindate

Edited February 13, 2013 by karimali831

[Jessica]

I think with a sub select you leave out the "AS"

[Jessica]

Edit: with table aliases you don't use AS. Just column Aliases.

[karimali831]

All done, thanks all :happy-04:

[Barand]

I'll mark as solved then

[karimali831]

I have question, I noticed it duplicates if you submit post by clicking the button twice.

How can I prevent this?

[shlumph]

I have question, I noticed it duplicates if you submit post by clicking the button twice.

How can I prevent this?

For starters, disable the submit button when the form is submitted. Then, in your processing script, check for cupid and matchno before you insert. Also, set up a UNIQUE index on those columns in your database. You will then be dup-proof