INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Marking duplicate records

Marking duplicate records

(OP)
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?

CODE

$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
www.studiosoft.co.uk

RE: Marking duplicate records

Hi

Like this ?

CODE --> MySQL

update imtable
join (
    select
    img, num

    from imtable

    group by img
) foo on foo.img = imtable.img and foo.num = imtable.num

set imtable.duped = 1 

Feherke.
feherke.ga

RE: Marking duplicate records

(OP)
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.

CODE

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
www.studiosoft.co.uk

RE: Marking duplicate records

(OP)
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.

CODE

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
www.studiosoft.co.uk

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close