"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/28/2004 10:21:43 AM:
> [snip] > UPDATE table1 > LEFT JOIN table2 ON table2.ID=table2.ID_table1 > SET table2.value=table2.value-IF(table1.condition=7,1,0), > table1.condition=8 > WHERE table1.ID=$id > > Is this behavior correct? > [/snip] > > In the sense that it did what was given to it in the query, yes. From > http://www.mysql.com/update > > "If you use a multiple-table UPDATE statement involving InnoDB tables > for which there are foreign key constraints, the MySQL optimizer might > process tables in an order that differs from that of their parent/child > relationship. In this case, the statement will fail and roll back. > Instead, update a single table and rely on the ON UPDATE capabilities > that InnoDB provides to cause the other tables to be modified > accordingly. " > > If you are using MyISAM style tables you should never attempt multiple > table updates without triggers and/or stored procedures to update the > subsequent tables. > That may be good advice IF you have a MySQL version that supports triggers and stored procedures. And I know for a fact that I do not want to nor should I need to code a trigger or SP EACH and EVERY time I want to update two tables at one time or use the values of one table to update another. I believe your answer was neither on topic nor very useful. That is an excellent warning you quoted from the manual but I do not believe that it applies to this particular problem. I also humbly disagree with your analysis of the warning as neither triggers nor stored procedures are in production MySQL, yet, but multiple table updates are. (I am operating on the belief that triggers and stored procedures are part of the 5.x release that is still in testing and development and not part of 4.1.x. I could be mistaken.) Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
