Marking duplicate records 1

[audiopro]

I am using the following code in Perl to mark distinct records in a table.
I am actually looking for duplicates but marking them this way leaves duplicates unmarked and it is those I process later.

The first select obtains a record set and then that record set is used to mark the appropriate records.

Is it possible to combine those quesries into a single one to speed the process up?

	$sql="SELECT IMG, NUM FROM $IMTABLE GROUP BY IMG";
	$sth=$dbh->prepare($sql);
	$sth->execute();
	$rv=$sth->rows;
	while(@results=$sth->fetchrow_array()){
		$sql1="UPDATE $IMTABLE SET DUPED=1 WHERE NUM=$results[1]";
		$sth1=$dbh->prepare($sql1);
		$sth1->execute();
		$rv1=$sth1->rows;
	}

Keith

http://www.studiosoft.co.uk

 

[feherke]

Hi

Like this ?

[b]update[/b] imtable
[b]join[/b] [teal]([/teal]
    [b]select[/b]
    img[teal],[/teal] num

    [b]from[/b] imtable

    [b]group by[/b] img
[teal])[/teal] foo [b]on[/b] foo[teal].[/teal]img [teal]=[/teal] imtable[teal].[/teal]img [b]and[/b] foo[teal].[/teal]num [teal]=[/teal] imtable[teal].[/teal]num

[b]set[/b] imtable[teal].[/teal]duped [teal]=[/teal] [purple]1[/purple]

Feherke.
feherke.ga

 

[audiopro]

Thanks
I thought that creating a temp table would be the way but I would not have had a clue how to approach it.
I can't see a typo but the query returns the dreaded -1.

UPDATE IMAGES2 JOIN(
SELECT IMG,NUM FROM IMAGES2 GROUP BY IMG)
FOO ON FOO.IMG = IMAGES2.IM AND FOO.NUM = IMAGES2.NUM 
SET IMAGES2.DUPED=1

Keith

http://www.studiosoft.co.uk

 

[audiopro]

It did work ok on another table, not sure why it failed but all is good, thanks
That has speeded the job up a lot.

update images join (select img, num from images group by img) foo on foo.img = images.img and foo.num = images.num set images.duped = 1

Keith

http://www.studiosoft.co.uk