RE: Multi table update
[snip] I read 6) as saying that until you complete all of the calculations on a row, you don't change the data for the row. I could even interpret this as saying that you don't update ANY row on the table until you have completed the calculations for ALL of the rows. I read 7) as saying that you compute all new values based on a fixed set of initial values, the . Am I just totally confused? I do not believe that any of these specification allows the engine to "see" a change to a row value until after the row (or even the entire statement, depending on how you want to read the clauses dealing with s or ) completes it's processing. [/snip] You may be confused, as the interpretation is quite the same for several DB manufacturers. In the case of InnoDB tables I am updating each row per the query... UPDATE [order of tables] SET [for each row table in order] Once the query has performed the operation on all rows affected the COMMIT occurs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/30/2004 05:20:49 PM: > [snip] > > Anyhow, it has been a good discussion. No bugs were uprooted and > > everyone's understanding may have risen a notch. After a couple of > > decades using SQL I still find interesting topics to discuss. > > Yes, I too have enjoyed this conversation. Thank you for thinking out > loud with me on such an important topic. I have one last pitch to make, > though, so I don't think you are quite off the hook yet. ;-) > > I went looking and found a nearly finished copy of the SQL-2003 spec > (but not the official one) at > http://www.wiscorp.com/sql/sql_2003_standard.zip (subfile: > 5WD-02-Foundation-2003-09.pdf). I can't imagine the final draft changing > too much from what I quote below. > [/snip] > > Most manufacturers are using ANSI SQL 99 IIRC (ymmv) which is available > from ANSI (http://www.ansi.org) for a small fee. It generally takes a > few years for standards to become implemented by mfgs. > > I think that you may be confusing search conditionals and equation > operations too... > > WHERE foo <= bar is a search condition > > IF(t1.foo=t2.bar, 2, 1) is an equation OK, here is the same section of SQL99 ( http://www.ncb.ernet.in/education/modules/dbms/SQL99/ansi-iso-9075-2-1999.pdf) ISO/IEC 9075-2:1999 (E) 14.10 (p 687) 9) The of each is effectively evaluated for each row of T before any row of T is updated. 10) A specifies one or more object columns and an update value. An object column is a column identified by an in the . The update value is the value specified by the contained in the . NOTE 314 â The data values allowable in the object rows may be constrained by a WITH CHECK OPTION constraint. The effect of a WITH CHECK OPTION constraint is defined in the General Rules of Subclause 14.22, ââEffect of replacing some rows in a viewed tableââ. I read 9) as saying that all of the source values, , to be used to calculate each term of each shall be determined before any row updates occur. I read 10) as saying that all column changes computed by the terms of the shall be based on the values stored in the Would a quote from SQL-92 do? (Sorry but I have lost it's source URL.) X3H2-92-154/DBL CBR-002 13.10 (p. 395) 6) The s are effectively evaluated for each row of T before updating any row of T. 7) A specifies an object column and an update value of that column. The object column is the column identified by the in the . The update value is the value specified by the . Note: The data values allowable in the object row may be con- strained by a WITH CHECK OPTION constraint. The effect of a WITH CHECK OPTION constraint is defined in the General Rules of Subclause 11.19, "". I read 6) as saying that until you complete all of the calculations on a row, you don't change the data for the row. I could even interpret this as saying that you don't update ANY row on the table until you have completed the calculations for ALL of the rows. I read 7) as saying that you compute all new values based on a fixed set of initial values, the . Am I just totally confused? I do not believe that any of these specification allows the engine to "see" a change to a row value until after the row (or even the entire statement, depending on how you want to read the clauses dealing with s or ) completes it's processing. No, I do not believe I have confused search conditionals with assignment or equation operators. The term refers to updates that occur as part of a stand-alone statement (UPDATE ... SET ... WHERE ...) as compared to which occurs within the processing of a cursor (WHERE = searched, CURSOR = positioned). Each type of update is handled distinctly in all three of the SQL specifications I have available to me. To me the MySQL implementation of UPDATE processing does not adhere to these standards. I can't wait for the holidays to end so that the developers can weigh in on this issue. Happy new year! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Multi table update OT
[snip] ...tons o' stuff... [/snip] BTW...Happy New Year! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
[snip] > Anyhow, it has been a good discussion. No bugs were uprooted and > everyone's understanding may have risen a notch. After a couple of > decades using SQL I still find interesting topics to discuss. Yes, I too have enjoyed this conversation. Thank you for thinking out loud with me on such an important topic. I have one last pitch to make, though, so I don't think you are quite off the hook yet. ;-) I went looking and found a nearly finished copy of the SQL-2003 spec (but not the official one) at http://www.wiscorp.com/sql/sql_2003_standard.zip (subfile: 5WD-02-Foundation-2003-09.pdf). I can't imagine the final draft changing too much from what I quote below. [/snip] Most manufacturers are using ANSI SQL 99 IIRC (ymmv) which is available from ANSI (http://www.ansi.org) for a small fee. It generally takes a few years for standards to become implemented by mfgs. I think that you may be confusing search conditionals and equation operations too... WHERE foo <= bar is a search condition IF(t1.foo=t2.bar, 2, 1) is an equation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/30/2004 03:50:24 PM: > [snip] > ... a bunch of stuff > [/snip] > > It alomost sounds as if you're coming from a M$ campwhat you > describe are VIEWS. In a way it is, an internally generated view of the source/destination tables. > > Anyhow, the ANSI SQL standards specify some of the behaviors we have > been talking about. Many DB manufacturers use the standards and > implement other "features" like you describe that fall outside of the > standards. Can you send me a link to those, I would like to read them so that I can stay up to date. I was able to find a version of SQL-2003 (see below), which one are you thinking of? > > I will repeat that with regards to the actions that you describe > concerning variables that you would be looking for something called a > CONSTANT. Variables are just that...variable. > And they will change their value to match the last update applied to it. But they don't change their values (for calculation purposes) until the engine finishes the row. > Anyhow, it has been a good discussion. No bugs were uprooted and > everyone's understanding may have risen a notch. After a couple of > decades using SQL I still find interesting topics to discuss. Yes, I too have enjoyed this conversation. Thank you for thinking out loud with me on such an important topic. I have one last pitch to make, though, so I don't think you are quite off the hook yet. ;-) I went looking and found a nearly finished copy of the SQL-2003 spec (but not the official one) at http://www.wiscorp.com/sql/sql_2003_standard.zip (subfile: 5WD-02-Foundation-2003-09.pdf). I can't imagine the final draft changing too much from what I quote below. (book page: 849, PDF page 873) * begin excerpts *** ISO/IEC 9075-2:2003 (E) 14.11 5) Case: a) If contains ONLY, then Case: i) If a is not specified, then all rows of T for which there is no subrow in a proper subtable of T are the subject rows. ii) If a is specified, then it is applied to each row of T with the exposed s or s of the bound to that row, and the subject rows are those rows for which the result of the is True and for which there is no subrow in a proper subtable of T. The is effectively evaluated for each row of T before updating any row of T. Each in the is effectively executed for each row of T and the results used in the application of the to the given row of T. If any executed contains an outer reference to a column of T, then the reference is to the value of that column in the given row of T. b) Otherwise, Case: i) If a is not specified, then all rows of T are the subject rows. ii) If a is specified, then it is applied to each row of T with the exposed of the bound to that row, and the subject rows are those rows for which the result of the is True. The is effectively evaluated for each row of T before any row of T is updated. Each in the is effectively executed for each row of T and the results used in the application of the to the given row of T. If any executed contains an outer reference to a column of T, then the reference is to the value of that column in the given row of T. NOTE 389 â outer reference is defined in Subclause 6.7, ââ. 6) If T is a base table, then each subject row is also an object row; otherwise, an object row is any row of a leaf generally underlying table of T from which a subject row is derived. 7) Equivalent s shall not appear more than once in a . 9) The of each is effectively evaluated for each row of T before any row of T is updated. 10) For each subject row, a candidate new row is constructed by copying the subject row and updating it as specified by each by applying the General Rules of Subclause 14.12, ââ. ** end excerpts *** If I interpret the above excerpted section and the other (referenced) sections appropriately (which I may not be, after such a quick read) it seems as though the SQL engine is supposed to determine the (which may be composed of multiple tables, views, and/or subqueries) then apply changes to each row of the by starting from a copy of the subject row and transforming it according to the then replacing that row in the with the results of the transformations. To be even more succinct: Start from a snapshot of all initial values and construct a new row based on values from the snapshot. (much as each generation of a "cellular automata"-baed simulation is computed). By my interpretation of these rules, I believe that MySQL fails to follow specification as Kai's sample data demonstrates. The failure is because changed data is immediately available as source data even though all of the row's transformations are not complete. The transformation to be applied to any column is not insulated from the results of the transformations applied to any of columns listed before it in the . However, I
RE: Multi table update
[snip] ... a bunch of stuff [/snip] It alomost sounds as if you're coming from a M$ campwhat you describe are VIEWS. Anyhow, the ANSI SQL standards specify some of the behaviors we have been talking about. Many DB manufacturers use the standards and implement other "features" like you describe that fall outside of the standards. I will repeat that with regards to the actions that you describe concerning variables that you would be looking for something called a CONSTANT. Variables are just that...variable. Anyhow, it has been a good discussion. No bugs were uprooted and everyone's understanding may have risen a notch. After a couple of decades using SQL I still find interesting topics to discuss. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/30/2004
01:32:20 PM:
> [snip]
> I understand that is the behavior of the assignment operator. That's why
> I used it. ;-) I expected the results to be 19 and 6 not 19 and 20.
> This is just another example of the serialization of evaluation that
> occurs while processing a single row of data. I have the opinion that
> while processing that row, the variable @testvalue should keep its
> original value and only be updated when moving to the next
> row(statement).
> [/snip]
>
> This has nothing to do with serialization, you have reassigned the
> variable value, and like any other programming language the new value is
> valid until it is reset by something else, so any subsequent operation
> will use that value. I believ that what you would like here is a
> CONSTANT
>
> [snip]
> If evaluation is serialized in a SELECT statement based on the order in
> which they are listed, then why aren't they evaluated in that order
> during the processing of a SET clause? Why does the query engine seem to
> revert to processing changes in "table column order" and not maintain
> the "statement sequence order" as specified by the user? That could
> imply that I need to change the design of my tables so that my update
> statements will execute in the correct order. If only the changes had
> happened in the sequence specified in Kai's original SET statement...
> (sigh) 8-D
> [/snip]
>
> It is because you are defining the order of the update in the UPDATE
> statement itself;
>
> UPDATE t2, t1, t0
>
> You have now specified the order of the update regardless of the actual
> updates that occur in the SET statements. SETs do not define order, even
> in single table/multiple column updates. If I do
I don't visualize it that way. I see the UPDATE ... JOIN ... portion of
the statement as defining an internally-constructed virtual table that
consists of all of the columns of each table participating in this
particular statement. This table is "populated with records" (mapped to
the actual data records) according to the inclusiveness (inner or outer)
and direction (left or right) of the JOIN clause and according to all of
the conditions established in the ON clause(s). The effect is, there is a
virtual table that becomes the target/source of the UPDATE or SELECT
statement being executed.
Now, each row of the virtual table must also be evaluated according to the
conditions specified in the WHERE clause (optimizations do exist that
shortcut out many row-by-row comparisons). For those rows that made it to
the table (from the JOINS) and pass the WHERE conditions, the column
transformations (formulas, simple reads, or assignments) specified in the
SET/SELECT clause are applied and those changes are either cached (until
the transaction commits or gets rolled back) or are committed to disk on a
row-by-row basis until we either run out of rows in the virtual table or
encounter an exception.
I think that where we truly disagree is about the order in which the
column transformations should be made. I say that each transformation
performed during the processing of a row should be based on the values
present when the processing of that row starts and performed in the order
specified in the SET/SELECT clause. If I understand you correctly, you are
saying that the transformations must be performed in the column order of
the virtual table and not in the sequence specified in the statement being
executed.
I also disagree with you that the column order of the virtual table should
have any effect on the final results. To use another variable-based
example (this is based on the principle that the value of an assignment
operation is the value that was assigned):
SET @Basevalue = 10;
SELECT @Basevalue:= @Basevalue + 10,@Basevalue:= @Basevalue +
10,@Basevalue:= @Basevalue + 10;
I expect the results {20,20,20} not {20,30,40} because for each formula,
@Basevalue (originally, on entry) is 10 not the results of the formula
that happened to be listed before it in the statement. If I repeated the
SELECT statement I would get {30,30,30} because on entry to that statement
@Basevalue had the initial value of 20. On exit, @Basevalue would be 30.
> SELECT t2.foo, t1.bar
>
> my results come out in the order specified in the SELECT statement, not
> the order in the FROM or subsequent condition statements...so the
> behavior really is no different between SELECT and UPDATE.
>
>
I agree, both SELECT and UPDATE (mis-)behave the same way.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
RE: Multi table update
[snip] I understand that is the behavior of the assignment operator. That's why I used it. ;-) I expected the results to be 19 and 6 not 19 and 20. This is just another example of the serialization of evaluation that occurs while processing a single row of data. I have the opinion that while processing that row, the variable @testvalue should keep its original value and only be updated when moving to the next row(statement). [/snip] This has nothing to do with serialization, you have reassigned the variable value, and like any other programming language the new value is valid until it is reset by something else, so any subsequent operation will use that value. I believ that what you would like here is a CONSTANT [snip] If evaluation is serialized in a SELECT statement based on the order in which they are listed, then why aren't they evaluated in that order during the processing of a SET clause? Why does the query engine seem to revert to processing changes in "table column order" and not maintain the "statement sequence order" as specified by the user? That could imply that I need to change the design of my tables so that my update statements will execute in the correct order. If only the changes had happened in the sequence specified in Kai's original SET statement... (sigh) 8-D [/snip] It is because you are defining the order of the update in the UPDATE statement itself; UPDATE t2, t1, t0 You have now specified the order of the update regardless of the actual updates that occur in the SET statements. SETs do not define order, even in single table/multiple column updates. If I do SELECT t2.foo, t1.bar my results come out in the order specified in the SELECT statement, not the order in the FROM or subsequent condition statements...so the behavior really is no different between SELECT and UPDATE. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
[snip] So the question is not, if a multi table update should be implemented serialized. The answer is clear: yes it should. The question is, on which data the subsequent updates should work. [/snip] It should work on the data specified in the update query. Each set statement is isolated and the join applies to the set statement at hand. update t2, t1, t0 set t2.value=IF(t1.value='foo', 1, 0) <--updates based on these conditions && join , t1.value='bar' <--updates based on these conditions && join , t0.value=IF(t1.value='bar', 5, 2) <--updates based on these conditions && join WHERE t2.id = t1.id AND t1.id = t0.id <-- all updates based on these conditions Now, there was a bug that pointed out earlier concerning joins in multi-table updates, but this has been fixed in version 4.1.x ^ I may not be understanding you still, my apologies if not. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/30/2004 11:43:43 AM: > [snip] > I hope I am not so naive that Kai and I are the only two who sees a > problem with this behavior. Some of you may think this is a "feature" > but I do not like it because that means that calculational errors will > occur simply by inverting column positions (putting one before the > other) within a statement. If anyone has links to any pages on database > theory that explains why it is proper to serialize the calculations > WITHIN a single SQL statement, I need to read them because I have been > operating under some mistaken beliefs for a long time. > > This is a real "house of cards" and I am a bit uncomfortable with the > situation. I am also surprised this hasn't come up more often. I will be > forwarding this to the internals list to get more eyes on the issue. > Hopefully one of them can explain why this is "correct" behavior and I > can just settle down and just be more deliberate in how I construct my > statements. > [/snip] > > I am curious to see what the internals list says on this as well. A > reading of SQL 92 and 99 support serialization behavior because "atomic" > behavior in this type of operation is likely ambiguous at best. On > several other implementations (other DBs) of multi-table updates the > behavior is exactly the same as this. There was a bug, > http://bugs.mysql.com/bug.php?id=1038 , that had something to do with th > join operations, but the bug has been fixed. > > Careful and deliberate construction of SQL statements should always > occur and be tested thouroughly prior to any implementation in a > production environment. And make sure to EXPLAIN each and every query > when testing. > > As to your second concern, variables, let us explore why your query > behaves as it does. > > >SET @testvalue=5; > >Select @testvalue:=19, @testvalue+1 as bump; > >++--+ > >| @testvalue:=19 | bump | > >++--+ > >| 19 | 20 | > >++--+ > > > > From http://dev.mysql.com/doc/mysql/en/Variables.html > > "You can also assign a value to a user variable in statements other than > SET. In this case, the assignment operator must be := and not = because > = is treated as a comparison operator in non-SET statements:" > > So you set @testvar to be 5, then when you SELECTed it you performed a > reassignment (@testvar:=19) and then bumped it. This is expected > behavior, so your reversal gives you the expected behavior because you > performed the re-assignment after you bumped the original value. I understand that is the behavior of the assignment operator. That's why I used it. ;-) I expected the results to be 19 and 6 not 19 and 20. This is just another example of the serialization of evaluation that occurs while processing a single row of data. I have the opinion that while processing that row, the variable @testvalue should keep its original value and only be updated when moving to the next row(statement). If evaluation is serialized in a SELECT statement based on the order in which they are listed, then why aren't they evaluated in that order during the processing of a SET clause? Why does the query engine seem to revert to processing changes in "table column order" and not maintain the "statement sequence order" as specified by the user? That could imply that I need to change the design of my tables so that my update statements will execute in the correct order. If only the changes had happened in the sequence specified in Kai's original SET statement... (sigh) 8-D Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Multi table update
Jay Blanchard schrieb: [snip] I hope I am not so naive that Kai and I are the only two who sees a problem with this behavior. Some of you may think this is a "feature" but I do not like it because that means that calculational errors will occur simply by inverting column positions (putting one before the other) within a statement. If anyone has links to any pages on database theory that explains why it is proper to serialize the calculations WITHIN a single SQL statement, I need to read them because I have been operating under some mistaken beliefs for a long time. This is a real "house of cards" and I am a bit uncomfortable with the situation. I am also surprised this hasn't come up more often. I will be forwarding this to the internals list to get more eyes on the issue. Hopefully one of them can explain why this is "correct" behavior and I can just settle down and just be more deliberate in how I construct my statements. [/snip] I am curious to see what the internals list says on this as well. A reading of SQL 92 and 99 support serialization behavior because "atomic" behavior in this type of operation is likely ambiguous at best. On several other implementations (other DBs) of multi-table updates the behavior is exactly the same as this. I don't think it is clear, what Shawn's and my point is. It is clear, that a multi table update will be executed in a serialized manner. I don't want to think of the difficulties in implementing a true atomic multi table update. But it is not clear, on which data the subsequent updates work. Currently the first update uses the initial data. The second uses the initial data and the updated data from the first table. The third update sees the updated data from the first two tables and the initial data for the remaining, etc... Here comes the "house of cards" into play: - What if an upgraded optimizer decides to swap table 4 and table 6 in an update line. A query on the same data could perform differently. Or on the other side an optimizer will never be allowed to change any order in the update and every join is automatically "straight". - What about the equivalence in a SELECT between FROM table1 LEFT JOIN table2 and FROM table2 RIGHT JOIN table1 Doesn't this apply to updates, because in the first case the update order is table1 followed by table2 and vice versa in the second case. So the question is not, if a multi table update should be implemented serialized. The answer is clear: yes it should. The question is, on which data the subsequent updates should work. In my opinion every subsequent update should work on the initial data. - An optimizer can perform the updates in any order without changing their result. - From the clients point of view the update acts atomically. There is initial data, an update and then changed data. There is no intermediate state during the update that the client can get / must be aware of. - The equivalence between severel joins remains. Greetings Kai -- Kai Ruhnau Software t a r g e t systemelectronic gmbh kölner str. 99 42651 Solingen germany Tel: +49 (0)212 22 20 9 - 710 Fax: +49 (0)212 20 10 45 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
[snip] I hope I am not so naive that Kai and I are the only two who sees a problem with this behavior. Some of you may think this is a "feature" but I do not like it because that means that calculational errors will occur simply by inverting column positions (putting one before the other) within a statement. If anyone has links to any pages on database theory that explains why it is proper to serialize the calculations WITHIN a single SQL statement, I need to read them because I have been operating under some mistaken beliefs for a long time. This is a real "house of cards" and I am a bit uncomfortable with the situation. I am also surprised this hasn't come up more often. I will be forwarding this to the internals list to get more eyes on the issue. Hopefully one of them can explain why this is "correct" behavior and I can just settle down and just be more deliberate in how I construct my statements. [/snip] I am curious to see what the internals list says on this as well. A reading of SQL 92 and 99 support serialization behavior because "atomic" behavior in this type of operation is likely ambiguous at best. On several other implementations (other DBs) of multi-table updates the behavior is exactly the same as this. There was a bug, http://bugs.mysql.com/bug.php?id=1038 , that had something to do with th join operations, but the bug has been fixed. Careful and deliberate construction of SQL statements should always occur and be tested thouroughly prior to any implementation in a production environment. And make sure to EXPLAIN each and every query when testing. As to your second concern, variables, let us explore why your query behaves as it does. >SET @testvalue=5; >Select @testvalue:=19, @testvalue+1 as bump; >++--+ >| @testvalue:=19 | bump | >++--+ >| 19 | 20 | >++--+ > >From http://dev.mysql.com/doc/mysql/en/Variables.html "You can also assign a value to a user variable in statements other than SET. In this case, the assignment operator must be := and not = because = is treated as a comparison operator in non-SET statements:" So you set @testvar to be 5, then when you SELECTed it you performed a reassignment (@testvar:=19) and then bumped it. This is expected behavior, so your reversal gives you the expected behavior because you performed the re-assignment after you bumped the original value. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi table update
[EMAIL PROTECTED] schrieb: I snipped the discussion down to the SQL-statements with some explanational text. I hope it does not break reading. [snip] UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID IN (1,2) fails to update table2.value because by the time it gets to that column (as part of the internal JOIN tableset), table1.condition is already 8 (even though that change was listed AFTER the conditional IF() in the SET clause) This worked: UPDATE table1 LEFT JOIN table2 ON table2.ID_table1=table1.ID AND table1.condition=7 SET table2.value= table2.value-1, table1.condition=8 WHERE table1.ID IN (1,2); and so did: UPDATE table2 INNER JOIN table1 ON table2.ID_table1=table1.ID SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID IN (1,2) I think that's improper behavior. I agree with Kai, the changes should not occur in serial but in parallel based on the initial values of the JOINed tables. I also disagree with this behavior: SET @testvalue=5; Select @testvalue:=19, @testvalue+1 as bump; ++--+ | @testvalue:=19 | bump | ++--+ | 19 | 20 | ++--+ [snip] SET @testvalue=5; Select @testvalue+1 as bump, @testvalue:=19; +--++ | bump | @testvalue:=19 | +--++ |6 | 19 |(not the same results, but I only changed the column order) +--++ [snip] I have also long thought that: UPDATE table1 LEFT JOIN table2 ... would be OPERATIONALLY equivalent to: UPDATE table2 RIGHT JOIN table1 ... However, with this calculation serialization behavior, I fear it won't be. [snip] Just in case, I filled bug report number #7590. Feel free to add your comment. Greetings Kai -- Kai Ruhnau Software t a r g e t systemelectronic gmbh kölner str. 99 42651 Solingen germany Tel: +49 (0)212 22 20 9 - 710 Fax: +49 (0)212 20 10 45 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/30/2004 07:59:35 AM: > [snip] > "atomically" from my applications point of view means: > I have a snapshot of my data (due to InnoDB this does not change > hiddenly during > my session). I send a query to the database. I get the result / changed > data. > And everything between sending and the receiving the result I expect to > be > handled at once. I know MySQL cannot handle handle the query as one big > block > behind the doors, but I wonder why I / my application has to know about > these > internals in this case. > > > UPDATE statements > > work with the JOIN order and update each table in succession. > Dependent > > on the number of rows affected you may see both tables locked > > (transactionally) but note a delay in the update of table1. > > My application does not see this and that's my point. > In case of this query I have to know, that there is more than the data > before > the query and the data after the query. There is some intermediate state > that I > must be aware of. > [/snip] > > Since I am not aware of your application language I can only speak to > the C style languages, including scripting languages like PHP. Any of > these will see the query as the sum of its parts, not as individual > actions. If you need to see those things you will have to write seperate > queries for each event you wish to see. > Jay, I think my problem with the behavior you found documentation for (not in the MySQL docs, but in Paul's book?) is that it documents that an UPDATE doesn't work on the ENTIRE JOIN as a whole but on each table individually. Here is a repost of Kai's test case: CREATE TABLE table1 ( ID int(10) unsigned NOT NULL default '0', condition int(10) unsigned NOT NULL default '0' ) TYPE=InnoDB; INSERT INTO table1 VALUES (1,7); INSERT INTO table1 VALUES (2,8); CREATE TABLE table2 ( ID int(10) unsigned NOT NULL default '0', ID_table1 int(10) unsigned NOT NULL default '0', value int(10) unsigned NOT NULL default '0' ) TYPE=InnoDB; INSERT INTO table2 VALUES (1,1,5); INSERT INTO table2 VALUES (2,2,2); If I do: SELECT * from table1 INNER JOIN table2 on table1.ID= table2.ID_table1; I get: ++---++---+---+ | ID | condition | ID | ID_table1 | value | ++---++---+---+ | 1 | 7 | 1 | 1 | 5 | | 2 | 8 | 2 | 2 | 2 | ++---++---+---+ Which is what I expect. However, when you do an UPDATE on that same JOIN, the engine breaks the JOIN back into it's individual pieces then serializes the changes between those pieces. UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID IN (1,2) fails to update table2.value because by the time it gets to that column (as part of the internal JOIN tableset), table1.condition is already 8 (even though that change was listed AFTER the conditional IF() in the SET clause) This worked: UPDATE table1 LEFT JOIN table2 ON table2.ID_table1=table1.ID AND table1.condition=7 SET table2.value= table2.value-1, table1.condition=8 WHERE table1.ID IN (1,2); and so did: UPDATE table2 INNER JOIN table1 ON table2.ID_table1=table1.ID SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID IN (1,2) I think that's improper behavior. I agree with Kai, the changes should not occur in serial but in parallel based on the initial values of the JOINed tables. I also disagree with this behavior: SET @testvalue=5; Select @testvalue:=19, @testvalue+1 as bump; ++--+ | @testvalue:=19 | bump | ++--+ | 19 | 20 | ++--+ On entry to the SELECT statement, @testvalue has a value of 5, on exit it will have the value of 19. The value of "bump" SHOULD be 6 because that is one more than the value that @testvalue had at the beginning of the statement/transaction but it isn't. This means that calculations are serialized in the order they are listed in the select statement. That means that both the SELECT and UPDATE statements are non-deterministic when it comes to column order. SET @testvalue=5; Select @testvalue+1 as bump, @testvalue:=19; +--++ | bump | @testvalue:=19 | +--++ |6 | 19 |(not the same results, but I only changed the column order) +--++ I hope I am not so naive that Kai and I are the only two who sees a problem with this behavior. Some of you may think this is a "feature" but I do not like it because that means that calculational errors will occur simply by inverting column positions (putting one before the other) within a statement. If anyone has links to any pages on database theory that explains why it is proper to serialize the calculations WITHIN a single SQL statement
RE: Multi table update
[snip] "atomically" from my applications point of view means: I have a snapshot of my data (due to InnoDB this does not change hiddenly during my session). I send a query to the database. I get the result / changed data. And everything between sending and the receiving the result I expect to be handled at once. I know MySQL cannot handle handle the query as one big block behind the doors, but I wonder why I / my application has to know about these internals in this case. > UPDATE statements > work with the JOIN order and update each table in succession. Dependent > on the number of rows affected you may see both tables locked > (transactionally) but note a delay in the update of table1. My application does not see this and that's my point. In case of this query I have to know, that there is more than the data before the query and the data after the query. There is some intermediate state that I must be aware of. [/snip] Since I am not aware of your application language I can only speak to the C style languages, including scripting languages like PHP. Any of these will see the query as the sum of its parts, not as individual actions. If you need to see those things you will have to write seperate queries for each event you wish to see. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
Zitat von Jay Blanchard <[EMAIL PROTECTED]>: > [snip] > Although my question goes in that direction, it is more about why this > statement > does not act atomically from my applications point of view. > > > update table2, table1 > > SET table2.value=table2.value-IF(table1.condition=7,1,0), > > table1.condition=8 > > WHERE table1.ID = table2.ID_table1 > [/snip] > > Define "atomically". If your definition is that each value in each table > is updated simultaneously that would be incorrect. "atomically" from my applications point of view means: I have a snapshot of my data (due to InnoDB this does not change hiddenly during my session). I send a query to the database. I get the result / changed data. And everything between sending and the receiving the result I expect to be handled at once. I know MySQL cannot handle handle the query as one big block behind the doors, but I wonder why I / my application has to know about these internals in this case. > UPDATE statements > work with the JOIN order and update each table in succession. Dependent > on the number of rows affected you may see both tables locked > (transactionally) but note a delay in the update of table1. My application does not see this and that's my point. In case of this query I have to know, that there is more than the data before the query and the data after the query. There is some intermediate state that I must be aware of. Take it this way: It does not matter if I write SELECT IF(table1.condition=7,1,0) FROM table1,table2 WHERE table1.ID=table2.ID_table1 or SELECT IF(table1.condition=7,1,0) FROM table2,table1 WHERE table1.ID=table2.ID_table1 The rows might be in a different order, but the rows I get are all the same in both cases. The same IF-statement in an UPDATE changes its value silently, if I change the order in which I join. Greetings Kai -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
[snip] Although my question goes in that direction, it is more about why this statement does not act atomically from my applications point of view. > update table2, table1 > SET table2.value=table2.value-IF(table1.condition=7,1,0), > table1.condition=8 > WHERE table1.ID = table2.ID_table1 [/snip] Define "atomically". If your definition is that each value in each table is updated simultaneously that would be incorrect. UPDATE statements work with the JOIN order and update each table in succession. Dependent on the number of rows affected you may see both tables locked (transactionally) but note a delay in the update of table1. Since I have been using SQL UPDATES and DELETES have been handled in JOIN order in multi-table operations. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
[snip] I would be deliriously happy if someone could explain WHY these statement _should_ have different results. If I am wrong and these UPDATE statements are supposed to provide different results, I would like to learn how I became so confused over something this fundamental. Until I get credible information to the contrary, I will stick with the hypothesis that this is a BUG in the execution engine. [/snip] Shawn, what makes an InnoDB table different from any other type of table with regards to the way UPDATE is processed? When you issue a multi-table update you must adhere to the properties of JOINs, hence they must be in the correct order. For reference, pages 234-236 of Paul DuBois' MySQL - Second Edition describe the importance of JOINs in the context of multi-table updates and deletes. Paul specifically mentions MySQL versions 4.x.x concerning these operations. Regardless of this being transactional the JOIN order is still important, because a transaction only fails or rollsback if all of the statements do not complete. In the OPs case the transaction did complete, one table was updated, negating the conditions for the second table's update. Updates occur in order of the JOIN. This would not be a bug IMHO. If you believe it to be you need to submit it to the powers that be for a response/resolution. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi table update
Zitat von Jay Blanchard > [snip] > And the query: > > UPDATE table1 > INNER JOIN table2 ON table2.ID_table1=table1.ID > SET table2.value=table2.value-IF(table1.condition=7,1,0), > table1.condition=8 > WHERE table1.ID IN (1,2) > > After that the value's in table2 are still 5 and 2. But I would expect > the 5 to > be a 4. > [/snip] > > It was concerning the warning I sent about InnoDB and the order of > precedence.the query to solve the problem is here My reading of the mentioned paragraph is another: It only describes the case when foreign key constraints may temporably be broken during a multi table update, so that the InnoDB engine does not perform the query. Although my question goes in that direction, it is more about why this statement does not act atomically from my applications point of view. > update table2, table1 > SET table2.value=table2.value-IF(table1.condition=7,1,0), > table1.condition=8 > WHERE table1.ID = table2.ID_table1 I wonder if a subselect can help here, too. Gretins Kai -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/28/2004 11:38:58 AM: > [snip] > And the query: > > UPDATE table1 > INNER JOIN table2 ON table2.ID_table1=table1.ID > SET table2.value=table2.value-IF(table1.condition=7,1,0), > table1.condition=8 > WHERE table1.ID IN (1,2) > > After that the value's in table2 are still 5 and 2. But I would expect > the 5 to > be a 4. > [/snip] > > It was concerning the warning I sent about InnoDB and the order of > precedence.the query to solve the problem is here > > update table2, table1 > SET table2.value=table2.value-IF(table1.condition=7,1,0), > table1.condition=8 > WHERE table1.ID = table2.ID_table1 > > Note that you put table1 first in your query above, so table 1 will get > updated first. Since that is the case the conditions for updating table2 > are never met. I disagree with your logic, not your statement. It works fine. Especially when using InnoDB, the table1 values should remain their original values throughout the entire statement. The row locking should prevent a visible change in a row of table1 between SET statements in the same UPDATE. If a row has a condition value of 7 coming into the SET clause, it should have that value for every element of the SET clause. The row should only be updated when the engine moves into the next row. The cumulative changes would be committed when the transaction commits. I interpreted the warning you quoted this way: An UPDATE statement may be evaluated in a sequence that could violate FOREIGN KEY constraints because the query optimizer does not favor parent tables over child tables in computing order of operations. I did not read it as saying: the order in which you join your tables will make a difference in the evaluation of the SET assignments during the execution of an UPDATE statement. Because CHANGING THE ORDER by which the tables are joined CHANGES THE RESULTS, this is definitely a BUG. These two statements SHOULD have the same results but DO NOT: update table1, table2 SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID = table2.ID_table1; Rows matched: 4 Changed: 1 Warnings: 0 update table2, table1 SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID = table2.ID_table1; Rows matched: 4 Changed: 2 Warnings: 0 I would be deliriously happy if someone could explain WHY these statement _should_ have different results. If I am wrong and these UPDATE statements are supposed to provide different results, I would like to learn how I became so confused over something this fundamental. Until I get credible information to the contrary, I will stick with the hypothesis that this is a BUG in the execution engine. Thanks in advance, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Multi table update
My Testing and results at the bottom. Suspect a buggy implementation of the IF() calculation (see tests and results). Kai Ruhnau <[EMAIL PROTECTED]> wrote on 12/28/2004 11:06:43 AM: > Zitat von [EMAIL PROTECTED]: > > > Can you provide us a test case (sample table definitions and sample data) > > ? > > Sure. > > -- > > CREATE TABLE table1 ( > ID int(10) unsigned NOT NULL default '0', > condition int(10) unsigned NOT NULL default '0' > ) TYPE=InnoDB; > > INSERT INTO table1 VALUES (1,7); > INSERT INTO table1 VALUES (2,8); > > CREATE TABLE table2 ( > ID int(10) unsigned NOT NULL default '0', > ID_table1 int(10) unsigned NOT NULL default '0', > value int(10) unsigned NOT NULL default '0' > ) TYPE=InnoDB; > > INSERT INTO table2 VALUES (1,1,5); > INSERT INTO table2 VALUES (2,2,2); > > -- > > And the query: > > UPDATE table1 > INNER JOIN table2 ON table2.ID_table1=table1.ID > SET table2.value=table2.value-IF(table1.condition=7,1,0), > table1.condition=8 > WHERE table1.ID IN (1,2) > > After that the value's in table2 are still 5 and 2. But I would > expect the 5 to > be a 4. > > MySQL: 4.0.20 > > Greetings > Kai This worked: UPDATE table1 LEFT JOIN table2 ON table2.ID_table1=table1.ID AND table1.condition=7 SET table2.value= table2.value-1, table1.condition=8 WHERE table1.ID IN (1,2); localhost.test>select * from table1; ++---+ | ID | condition | ++---+ | 1 | 8 | | 2 | 8 | ++---+ 2 rows in set (0.00 sec) localhost.test>select * from table2; ++---+---+ | ID | ID_table1 | value | ++---+---+ | 1 | 1 | 4 | | 2 | 2 | 2 | ++---+---+ 2 rows in set (0.00 sec) These did not: UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID IN (1,2); localhost.test>select * from table1; ++---+ | ID | condition | ++---+ | 1 | 8 | | 2 | 8 | ++---+ 2 rows in set (0.00 sec) localhost.test>select * from table2; ++---+---+ | ID | ID_table1 | value | ++---+---+ | 1 | 1 | 5 | | 2 | 2 | 2 | ++---+---+ 2 rows in set (0.00 sec) UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=IF(table1.condition=7,table2.value-1,table2.value), table1.condition=8 WHERE table1.ID IN (1,2); localhost.test>select * from table1; ++---+ | ID | condition | ++---+ | 1 | 8 | | 2 | 8 | ++---+ 2 rows in set (0.00 sec) localhost.test>select * from table2; ++---+---+ | ID | ID_table1 | value | ++---+---+ | 1 | 1 | 5 | | 2 | 2 | 2 | ++---+---+ 2 rows in set (0.00 sec) However when I tried the same update WITHOUT trying to update table1.condition in the same query, it worked every time: UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=IF(table1.condition=7,table2.value-1,table2.value) WHERE table1.ID IN (1,2); localhost.test>select * from table2; ++---+---+ | ID | ID_table1 | value | ++---+---+ | 1 | 1 | 4 | | 2 | 2 | 2 | ++---+---+ 2 rows in set (0.00 sec) UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=(table2.value-IF(table1.condition=7,1,0)) WHERE table1.ID IN (1,2); localhost.test>select * from table2; ++---+---+ | ID | ID_table1 | value | ++---+---+ | 1 | 1 | 4 | | 2 | 2 | 2 | ++---+---+ 2 rows in set (0.00 sec) UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=table2.value-IF(table1.condition=7,1,0) WHERE table1.ID IN (1,2); localhost.test>select * from table2; ++---+---+ | ID | ID_table1 | value | ++---+---+ | 1 | 1 | 4 | | 2 | 2 | 2 | ++---+---+ 2 rows in set (0.00 sec) So -- This may be a bug in the way that my version (4.1.1a-alpha-nt-log) and yours prioritizes the IF() calculation. I guess it is checking the value AFTER table1.condition gets set to 8. However, I would have also assumed that table1.condition would have held it's ORIGINAL value of 7 long enough to make your IF() condition true. Fortunately, I found a JOIN workaround that achieved the expected results. Let me know if you need any help adapting it to fit your actual tables. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Multi table update
[snip] And the query: UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID IN (1,2) After that the value's in table2 are still 5 and 2. But I would expect the 5 to be a 4. [/snip] It was concerning the warning I sent about InnoDB and the order of precedence.the query to solve the problem is here update table2, table1 SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID = table2.ID_table1 Note that you put table1 first in your query above, so table 1 will get updated first. Since that is the case the conditions for updating table2 are never met. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi table update
Zitat von [EMAIL PROTECTED]: > Can you provide us a test case (sample table definitions and sample data) > ? Sure. -- CREATE TABLE table1 ( ID int(10) unsigned NOT NULL default '0', condition int(10) unsigned NOT NULL default '0' ) TYPE=InnoDB; INSERT INTO table1 VALUES (1,7); INSERT INTO table1 VALUES (2,8); CREATE TABLE table2 ( ID int(10) unsigned NOT NULL default '0', ID_table1 int(10) unsigned NOT NULL default '0', value int(10) unsigned NOT NULL default '0' ) TYPE=InnoDB; INSERT INTO table2 VALUES (1,1,5); INSERT INTO table2 VALUES (2,2,2); -- And the query: UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID IN (1,2) After that the value's in table2 are still 5 and 2. But I would expect the 5 to be a 4. MySQL: 4.0.20 Greetings Kai -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
[snip] 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.) [/snip] Thank you for your assessment of my answer. It is too bad that your reply was also not very useful in the context of the OP's question. As a user of MySQL for several years I have found that multi-table updates can be done, but are very tricky, especially with regards to the order that the query is processed in. There is a good discussion of this at http://www.mysql.com/update You are correct in your belief that 4.1.x does not support triggers and or SP. Given that, I would suggest that the OP utilize his programming language to generate these actions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi table update
Can you provide us a test case (sample table definitions and sample data) ? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Kai Ruhnau <[EMAIL PROTECTED]> wrote on 12/28/2004 10:23:23 AM: > Zitat von [EMAIL PROTECTED]: > > > One reason may be due to your ON condition. > > > > ON table2.ID=table2.ID_table1 > > > > You reference table 2 twice and I doubt that on the table2 table id equals > > id_table1 very often. Because table2 is LEFT JOINed to table1, you will > > only be able to update the rows that match your ON condition. Again, that > > would mean that you have nothing to update which would cause no changes to > > table2 so it would appear that the IF() is always false. > > Hmm, I really should have read that query twice, sorry > > UPDATE table1 > INNER JOIN table2 ON table2.ID_table1=table1.ID > SET table2.value=table2.value-IF(table1.condition=7,1,0), > table1.condition=8 > WHERE table1.ID=$id > > Problem: table2.value is never changed, even if table1.condition > equals 7 before > the query. > > Greetings > Kai > > -- > This signature is left as an exercise for the reader. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >
RE: Multi table update
"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] >
Re: Multi table update
Zitat von [EMAIL PROTECTED]: > One reason may be due to your ON condition. > > ON table2.ID=table2.ID_table1 > > You reference table 2 twice and I doubt that on the table2 table id equals > id_table1 very often. Because table2 is LEFT JOINed to table1, you will > only be able to update the rows that match your ON condition. Again, that > would mean that you have nothing to update which would cause no changes to > table2 so it would appear that the IF() is always false. Hmm, I really should have read that query twice, sorry UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID=$id Problem: table2.value is never changed, even if table1.condition equals 7 before the query. Greetings Kai -- This signature is left as an exercise for the reader. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
[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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi-Table UPDATE Emulation
"Mike Brum" <[EMAIL PROTECTED]> wrote: > Hopefully someone can help me out with this - > > I have two tables in mySQL: comments and diary > > I want to populate a new field in comments with the current values of diary > - but only if they meet certain criteria. > > Now, the problem is this - my webserver is using an older version of mySQL > that doesn't support UPDATEs from multiple tables (that's not possible until > v4.0.4) and unfortunately, them updating the install isn't a possibility. I > know if it was v4.0.4+ I could juse use: > > UPDATE comments,diary > SET comments.diary_date=diary.date > WHERE comments.refid=diary.ID > > I also don't think I can use sub-selects in queries either. > > Btw - I'm currently running v3.23.58. Use programming language to retrieve data with SELECT statement and then do UPDATE. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multi-table update
Corey Kaye writes: > I'm wondering what is the best way to update one table based on another. Can joins >be performed for an update? > > update table1 set table1.field2 = table2.field2 where table1.field1 = table2.field1; > > The alternative seams to be: > select table2.field2 from table2; > update table2 set table2.field2 = $savedValue; > > which is very slow > > Thanks. > This feature will come in 4.0.2, with a different syntax: UPDATE join_table_list SET ... WHERE ... -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
