Re: Ref: WCL302 Subject: UPDATE multi-table current column value error

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,

-- 
For technical support contracts, go to https://order.mysql.com/?ref=msmi
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Mr. Sinisa Milivojevic <[email protected]>
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB
/_/  /_/\_, /___/\___\_\___/   Fulltime Developer and Support Coordinator
       <___/   www.mysql.com   Larnaca, Cyprus


-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]