a sql question anyone?

**Fuckin Bill** · 02-07-2005, 11:46 AM

I think the way you've got it there is probably the best you're going to get. You may be able to optimize code around that in the program, but I don't think you can really get any tighter than that with the sql query itself, since all the values and records are different.

**MGPspots** · 02-07-2005, 11:47 AM

Good question... but I don't think there's a quick way since they are different rows in the table. Haven't seen any sql looping commands...

**swedguy** · 02-07-2005, 11:49 AM

If you have different where clauses, that's the way you have to do it.

**Zester** · 02-07-2005, 11:51 AM

i thought i'd ask before I update 8747 rows

**Zester** · 02-07-2005, 11:51 AM

8748 that is (just made another sale while posting)

**swedguy** · 02-07-2005, 11:54 AM

hmm, there might be a way. Are you changing appear_order from one var to another var? Like all 1's to 5's or something similair?

**Warden** · 02-07-2005, 11:57 AM

on an update, if you are updating every single record just leave out the WHERE statement

**swedguy** · 02-07-2005, 11:59 AM

Originally posted by Warden

on an update, if you are updating every single record just leave out the WHERE statement

appear_order is different in his example, so that will not work. But what you're saying might work if for example all 1's are changed to 5's, 2 to 9 and so on.

**beergood** · 02-07-2005, 12:01 PM

I think what you're trying to do is called "Batch Updates".

**Warden** · 02-07-2005, 12:01 PM

Originally posted by swedguy

appear_order is different in his example, so that will not work. But what you're saying might work if for example all 1's are changed to 5's, 2 to 9 and so on.

Disregard. I just quick glanced at it and realized just now that there are unique values to that column.

**Zester** · 02-07-2005, 12:14 PM

Originally posted by Warden

Disregard. I just quick glanced at it and realized just now that there are unique values to that column.

yes , different values BUT the appear_id's are increasing values by 1 each time

**RefaStud** · 02-07-2005, 12:21 PM

if you are just updating the appear_id's and nothing else you can do something like this.

UPDATE records set appear_order=appear_order + 1;

that assumes you want to auto_increment the number by 1 in EVERY ROW in the DB;

**Zester** · 02-07-2005, 12:23 PM

Originally posted by RefaStud

if you are just updating the appear_id's and nothing else you can do something like this.

UPDATE records set appear_order=appear_order + 1;

that assumes you want to auto_increment the number by 1 in EVERY ROW in the DB;

nope, still need to specify ID

**Zester** · 02-07-2005, 12:26 PM

wait, the SQL query does'nt work. i get:

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 'UPDATE records set appear_order='2' WHERE id='19'; UPDATE recor

so it can't do more then 1 action in a query??

**RefaStud** · 02-07-2005, 01:40 PM

nope. you will have to do a loop through each of the id's you wan't to update.

my $sql = "update appear_count='?' where ID='?'";
my $sth = $dbh->prepare($sql);
foreach my $ID (@ids){
$sth->execute(APPEAR_COUNT, $ID);
}

a sql question anyone?

a sql question anyone?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment