Updating a mysql database2 | 22 from 01 08 2003 / 23 from 02 08 2003 / | | 3 |
If an update on a row would result in a violation of a primary key or unique index, the update on that row is not performed.
You could then do select @value;in order to see what the value was before you updated it My SQL uses Watcom (Oracle) syntax for UPDATE, so it's possible to write something like:update Table1 t1 join Table2 t2 on t1. When done, you "clear" the semaphore by UPDATE table SET value = 0 WHERE name = 'name' The assumption is that the UPDATE is "atomic" in that no concurrent access by another process can occur between testing and setting the value field.
[I have posted this in the Flow Control Functions page last year but I still see people asking how to update multiple rows.
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.