nlv11281 at natlab.research.philips.com
Thu Feb 2 03:30:27 EST 2006
> The script assumes that the first field of each table is a primary key, > and this is necessary for performing updates on the table. > In those cases where the tables don't have a primary key, or when the > primary key is composed of two fields, the update cannot be performed > correctly. Another solution is to use an update-where-all scheme (like Delphi uses for some table queries). This also helps detect race conditions (other people editing the same records) as a side effect. (unrelated to your question: I typically use the update-where-changed system so that concurrent edits are possible as long as they do not touch the same entries. It also gives you the opportunity to present the changes someone else made, and have the user decide on how to merge them.) So if you have a row where "SELECT * FROM customer" returns (51, "Mike", "MiLo Software"), and the user edits "Mike" into "Graham", the query to be run in update-where-all mode is: c.execute("UPDATE customer SET owner=%s WHERE customer_id=%s AND owner=%s AND name=%s", ("Graham", 51, "Mike", "MiLo Software")) This will work regardless of the DBMS used, and whether the table has a primary key at all (rows should still be unique though...) Mike.