[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]
