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
