Re: Ref: WCL302 Subject: UPDATE multi-table current column value error - Sinisa Milivojevic - com.mysql.lists.bugs

2 messages in com.mysql.lists.bugsRe: Ref: WCL302 Subject: UPDATE multi...

From	Sent On	Attachments
Colin Nelson	26 Nov 2003 08:04
Sinisa Milivojevic	26 Nov 2003 10:06

Subject:	Re: Ref: WCL302 Subject: UPDATE multi-table current column value error
From:	Sinisa Milivojevic (sin...@mysql.com)
Date:	11/26/2003 10:06:23 AM
List:	com.mysql.lists.bugs

Colin Nelson writes:

Hi there,

I need to do an update on a table using an existing column value:-

#---------SQL Script follows

[skip]

UPDATE XX, TT SET tt_c = tt_a, tt_a = xx_a, tt_b = tt_c - xx_a + tt_b WHERE tt_id = xx_id AND tt_id = 3;

#mysql> select * from TT; #+-------+------+------+------+ #| tt_id | tt_a | tt_b | tt_c | #+-------+------+------+------+ #| 1 | 40 | 10 | NULL | #| 2 | 40 | -30 | 0 | #| 3 | 40 | NULL | 0 | #+-------+------+------+------+ #3 rows in set (0.00 sec)

Hi1

First of all, I get a bit different results then you with 4.0.16:

tt_id tt_a tt_b tt_c 1 0 10 NULL 2 0 10 NULL 3 40 NULL 0

But, second and most important.

Multi-table update can use new values in the "main" table only, because in multi-table update there is only one table that is updated on the fly.

The remaining ones are updated after all values have been collected.

In some cases, like in the above one, not even one table is updated on the fly, due to the fact that first table in the join is not the one which is updated.

I have asked our decumentation department to update our manual on this issue accordingly.

Thank you for pointing to this documentation deficiency.

Sincerely,