how can i update many different rows

[andre3]

Hi guys, whats the easiest way to update many different rows inside a  table with one query,  for example; i have a table called site_pages with id, name, position and there are 4 rows in there with is 1, 2 , 3 , 4, and i need to update all row id  +  1.. how wud i do that? i tried using a while loop with a update statement inside it, but it update all the rows with the same number....  :-\

[revraz]

Post what you tried.  Sounds like your variable was empty.

[andre3]

here is it:


$sql = mysql_query("SELECT * FROM site_pages");
       while($row = mysql_fetch_array($sql))
{

$newid = $row[id] + 1;

mysql_query("UPDATE site_pages SET id='".$newid."'");

}

[hobeau]

Instead of doing a SELECT statement and then iterating through it with a loop, simply run the following sql command:

 

mysql_query("UPDATE site_pages SET id = (id + 1)");

 

If this is a primary key column, you may have to alter the table so that it is not a primary key temporarily while you run this query as it may produce duplicate keys thereby violating the primary key unique rule. Hope this helps!!!

[ToonMariner]

NEVER executre queries inside a loop - its the most horrible thing - simply highlights the lack of thought that has gone into someones 'code'.

[hobeau]

NEVER executre queries inside a loop - its the most horrible thing - simply highlights the lack of thought that has gone into someones 'code'.

 

well,, maybe not 'never',, there are some rare occasions, but for the most part,, ya your right ToonMariner lol.

[ToonMariner]

NOPE I'll stick with never

[andre3]

still doesnt work accurate an am still trying, any other suggestions?

[andre3]

i think i am getting somewher now,  one question, is there a way that i can change

 

the id value  thats into the bracked it a variable? reason is that when i run the script its updating all id numbrs uniquely but it updates them with the last id number example, if the last id number is 100 it adds that number to ever other id when the script runs, an i just need it to add a extra 1 to each id number each time.

 

example; ($originalposition + 1)

 

mysql_query("UPDATE site_pages SET id = (id + 1)");

 

?

[andre3]

i got it to work thanks guys