Mysql Update Everything Bit confusing

[krap 0]

i need to make a script that will take a number from every record in a mysql table, takeaway 1 from them all then replace them all in the right places. so if it looked like

Record 1 5

Record 2 8

Record 3 9

then i exectue the script then it turns into:

Record 1 4

Record 2 7

Record 3 8

i cant think of a way to do this so if you can help id be very greatful.

 

Thanks in advance

Andre

[Argoth 0]

I'm not sure if this will work, and there is probably a better way to do this but you might as well give it a try.

ID is the primary key of the table.
C1 is the column with the number to be subtracted.
C2 is the number you are subjection from.
T1 is the name of the table.

<?php$result = mysql_query("select ID, C1, C2 from T1");while($row = mysql_fetch_array($result)){  $row[2] = $row[2] - $row[1];  mysql_query("update T1 set C2 = '$row[2]' where ID = '$row[0]");}?>

Let me know if I screwed up and what the error is and I'll see if I can fix it....

[beeseven 0]

If the column is set as a number (INT, FLOAT, DOUBLE, etc.), then I think you can just do this:

mysql_query("UPDATE `tablename` SET `colname`=`colname`-1");

[Mike 0]

Uhh.. That's just a simple query like beeseven posted..

mysql_query("UPDATE tablename SET columnname=columnname - 1") or die(mysql_error());

It would just update the column as the column - 1.

[bjrn 0]

Yep, beeseven's and mike's querys should work, it only becomes tricky if the field is some sort of text, in which case you'd be forced to pull all data, convert the text to numbers, subtract one, and then store it (which would autoconvert it back to text).