a sql question anyone?

[Zester]

I wonder how many of you know SQL.

$sql="
UPDATE records set appear_order='3' WHERE id='19' ;
UPDATE records set appear_order='5' WHERE id='16' ;
UPDATE records set appear_order='4' WHERE id='17' ;
UPDATE records set appear_order='6' WHERE id='20' ;
UPDATE records set appear_order='2' WHERE id='18' ;
";

is there a better shorter way to do this query? can all these updated even be done at once?

[Fuckin Bill]

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]

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]

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

[Zester]

i thought i'd ask before I update 8747 rows

[Zester]

8748 that is (just made another sale while posting)

[swedguy]

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]

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

[swedguy]

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]

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

[Warden]

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]

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]

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]

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]

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]

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);
}