a sql question anyone?
 

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?

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.

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...

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

i thought i'd ask before I update 8747 rows

8748 that is (just made another sale while posting)

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?

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.

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

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

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

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??

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