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

Hi there,

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

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

CREATE TABLE TT(
tt_id INTEGER NOT NULL AUTO_INCREMENT,
tt_a INTEGER,
tt_b INTEGER,
tt_c INTEGER,
PRIMARY KEY (tt_id)
) TYPE = InnoDB;

INSERT INTO TT(tt_a, tt_b) VALUES(0,10);
INSERT INTO TT(tt_a, tt_b) VALUES(0,10);
INSERT INTO TT(tt_a, tt_b) VALUES(0,10);

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

CREATE TABLE XX(
xx_id INTEGER NOT NULL AUTO_INCREMENT,
xx_a INTEGER,
PRIMARY KEY (xx_id)
) TYPE = InnoDB;

INSERT INTO XX(xx_a) VALUES(40);
INSERT INTO XX(xx_a) VALUES(40);
INSERT INTO XX(xx_a) VALUES(40);

#mysql> select * from XX;
#+-------+------+
#| xx_id | xx_a |
#+-------+------+
#| 1 | 40 |
#| 2 | 40 |
#| 3 | 40 |
#+-------+------+
#3 rows in set (0.00 sec)


UPDATE TT SET tt_a = 40, tt_b = tt_a - 40 + tt_b where tt_id = 1;

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

#=========================================================== =============
# This result is as expected, because the manual says that the new value
# of tt_a is used. In order to use the old value, I use column tt_c
# to hold a temporary value
#=========================================================== =============

UPDATE TT SET tt_c = tt_a, tt_a = 40, tt_b = tt_c - 40 + tt_b where tt_id =
2;

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

#=========================================================== =============
# So the calculation works, tt_b's value is based on old value of tt_a.
# Now let's try this multi-table
#=========================================================== =============

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)

#=========================================================== =============
# The NULL of tt_c is propagated to tt_b. Not as expected.
#=========================================================== =============




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

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
/ /|_/ / // /\ \/ /_/ / /__ 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/bugs?unsub=gcdmb-bugs@m.gmane.org