A table that contains entries of different categories, in which an internal order needs to represented ( lets say a table with busstops on different routes).
Updating a result set
This can be done similarily : SET @pos=0; UPDATE busstops SET pos = ( SELECT @pos := @pos 1 ) WHERE route = 1 ORDER BY pos ASCChris H (chansel0049) I experienced a weird issue converting from 4 to 5.
A is a normal table, B is a temporary table: Worked in 4update A, B set A.population=B.pop_count where A.id=In version 5, however, the above query only updated one element while still matching "all"In 5 I had to do it like this:update A RIGHT JOIN B on A.id=set A.population=B.pop_count Updates all population counts correctly.[edit: RIGHT JOIN not LEFT JOIN...] Related to the post of Mohamed Hossam on May 9 2005 am A more general method to updtate more one row: UPDATE table SET f1='foo', f2= IF(f3=value,‘one’, IF(f3=value_bis,’two’,f2)) WHERE f5='afected' This set the values of field 'f2' according to the values of field 'f3' in the rows field f5 'afected'.
There is a page in the online documentation that explains safe mode entitled 'safe Server Startup Script'.
Suppose you have a table where each row is associated with a certain group (For example, orders are associated with the customers placing them) where each item WITHIN the group has a distinct number (For example, each person my have a sequence of competition results - each person, therefore, has a 1st, 2nd, 3rd... If you would like to renumber items within their group so that each has the same baseline (say 0), here is an example way to proceed: Create TEMPORARY Table Groups (Id INTEGER AUTO_INCREMENT PRIMARY KEY, Name VARCHAR(31), Group Id VARCHAR(31), Val Within Group INTEGER); INSERT INTO Groups VALUES (null, "Davy", "Boy", 2); INSERT INTO Groups VALUES (null, "Mary", "Girl", 2); INSERT INTO Groups VALUES (null, "Bill", "Boy", 5); INSERT INTO Groups VALUES (null, "Jill", "Girl", -3); INSERT INTO Groups VALUES (null, "Fred", "Boy", 3);# Find the lowest value for each group CREATE TEMPORARY TABLE Group Sum AS SELECT Group Id, MIN(Val Within Group) AS base Val FROM Groups GROUP BY Group Id;# create an index so my SQL can efficiently match ALTER TABLE Group Sum ADD UNIQUE (Group Id);# finally, make the baseline adjustment UPDATE Groups LEFT JOIN Group Sum USING (Group Id) SET Val Within Group=Val Within Group-base Val; SELECT * FROM Groups;# 1 Davy Boy 0# 2 Mary Girl 5# 3 Bill Boy 3# 4 Jill Girl 0# 5 Fred Boy 1#Each group ("Boy", "Girl") now has a (lowest) Val Within Group entry of 0.
I had a problem - a had to update a column "rate" but if the existince or new value is greater then 5 this "5" will be finally value in field.