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.
You can LEFT JOIN only those records you want to change. That way you can
avoid the IF() and not worry about updating any other records.
I think it should look something like this:
UPDATE table1
LEFT JOIN table2
ON table1.ID=table2.ID_table1
AND table1.condition=7
SET table2.value=table2.value-1,
table1.condition=8
WHERE table1.ID=$id
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Kai Ruhnau <[EMAIL PROTECTED]> wrote on 12/28/2004 04:22:34
AM:
> Hi,
>
> An SQL-statement that is conceptually equal to the following caused some
> astonishment.
>
> 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
>
> My problem is the IF condition, which alway evaluates to false no matter
> what conditions value was before the query. My explanation is, that
> MySQL first updates table1 and uses the changed values to update table2.
>
> In the meantime I solved it on application-level, but up to now I
> consindered a multi table update an atomic operation.
>
> Is this behavior correct?
>
> Greetings
> Kai
>
> --
> This signature is left as an exercise for the reader.
> Unsatz des Jahres:
> $POLITIKER ruft $PARTEI zur Geschlossenheit.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
