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/[email protected]