CODEWITHSUNDEEP
phpmysql
Chris
Chris

Reputation: 69

Update column with +1 increment based on columns with duplicate value

I have a mysql table with 3 columns (itemid, altid, counter)

I am having a difficult time looping through each row to update the counter column based on the number of duplicate itemid's.

Example of what I am trying to accomplish:

itemid | altid | counter
1        30      1
1        31      2
1        37      3
5        53      1
5        54      2
6        112     1
6        113     2
6        114     3
6        115     4

Notice where the itemid is duplicated, the counter is incrementing +1.

The list goes on about 2500 rows. My counter column is empty. I have had no luck with the increment inside a while loop to update in php because I don't know how to detect that duplicate value (or checking that previous value).

This is all I have right now (doesn't work):

$query = mysql_query("SELECT * FROM table") or die(mysql_error());
while ($row = mysql_fetch_assoc($query)){
$itemid = $row['itemid'];
$i=1;
    if($row['itemid']!=$itemid){
    $i=1;
    }
$id = $row['id'];
$query = mysql_query("UPDATE table SET counter='$i' WHERE id=$id") or die(mysql_error());
$i++;
}

I searched and haven't found much to help accomplish this. Any help, or push in the right direction would be very much appreciated!

Upvotes: 2

Views: 1278

Answers (2)

pozs
pozs

Reputation: 36234

There is a command especially for this (only in MySQL): INSERT ... ON DUPLICATE KEY UPDATE

Upvotes: 0

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324620

Try this MySQL:

SET @pos=0, @last=0;
UPDATE `table` SET `counter`=(
    @pos:=if(
        @last=`itemid`,
        @pos+1,
        (@last:=`itemid`) and 1
    )
) ORDER BY `itemid` ASC;

Upvotes: 6

Related Questions