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.
See Section 15.8.7, “Inno DB and FOREIGN KEY Constraints”. Records not matching the equijoin from table2 are marked with null.
You cannot update a table and select from the same table in a subquery. This facilitates to update table1 column with expression whose corresponding value from table2 is returned as NULL It took me a few minutes to figure this out, but the syntax for UPDATING ONE TABLE ONLY using a relationship between two tables in My SQL 4.0 is actually quite simple:update t1, t2 set t1.field = t2.value where t1= t2.that; It should be noted that even simple applications of UPDATE can conflict with the 'safe mode' setting of the mysql daemon.
Only the partitions (or subpartitions) listed are checked for matches, and a row that is not in any of these partitions or subpartitions is not updated, whether it satisfies the clause, are flagged as unsafe for statement-based replication.
(This is because the order in which the rows are updated determines which rows are ignored.) With this change, such statements produce a warning in the log when using statement-based mode and are logged using the row-based format when using mode.
Notes: That index addition is necessary because on larger tables my SQL would rather die than figure to (internally) index a single column join.