Updating more than one column
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'.
If UPDATE gives an error like this:"You are using safe update mode and you tried to update a table without.."..it may be that your file must be edited to disable safemode. In order for the change in the file to take effect, you must have permission to restart mysqld in the server OS environment.Here is a way to use multiple tables in your UPDATE statement, but actually copying one row values into the other, meaning, we're using the same table: UPDATE jobs AS to Table, jobs AS from Table SET to Table.job_type_id = from Table.job_type_id, to Table.job_company_id = from Table.job_company_id, to Table.job_source = from Table.job_source, WHERE (to Table.job_id = 6) AND (from Table.job_id = 1)--------------Pretty cool.What I'm doing here is copying the information I need from the row where job_id=1 to the row where job_id=6, on the same table.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.