Question 20734 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql multiple tables update – results inconsistent

UPDATE table1 alias1,table2 alias2  SET  
alias2.CURRENT_CD_BALANCE = alias2.CURRENT_CD_BALANCE - alias1.ORIGINAL_AMOUNT ,
alias1.COMMENT = concat('Sum of ',alias1.ORIGINAL_AMOUNT,' Cancelled') ,
alias1.BALANCE = 0,
alias1.ORIGINAL_AMOUNT = 0,
TRAN_TYPE = 'D'  
WHERE alias1.STORE_ID = alias2.STORE_ID AND 
alias1.ACCTNO = alias2.ACCTNO  AND 
alias1.AR_TRANS_ID = value1;


Query OK, 1 row affected (0.001 sec)
Rows matched: 2  Changed: 1  Warnings: 0

Before the above query is run alias1.ORIGINAL_AMOUNT is 900000 and alias2.CURRENT_CD_BALANCE is the 900000.
After the query is run tabel1.ORIGINAL_AMOUNT is set to zero however alias2.CURRENT_CD_BALANCE is unchanged (still value of 900000).
This suggests that alias1.ORIGINAL_AMOUNT is set to zero before the operation

alias2.CURRENT_CD_BALANCE = alias2.CURRENT_CD_BALANCE - alias1.ORIGINAL_AMOUNT`

is executed.
However the alias1.COMMMENT column has the value ‘Sum of 900000 Cancelled’, which suggests the initial value of alias1.ORIGINAL_AMOUNT was assigned to alias1.COMMENT before it was set to zero.
Why is this happening?
How should this query be structured so that the initial value of alias1.ORIGINAL_AMOUNT is subtracted from alias2.CURRENT_CD_BALANCE before it is set to zero?
The response from the server suggest that the two tables are

I’ve tried restructuring the query using joins without the alias. The combination (STORE_ID,ACCTNO) are unique keys on both tables AR_TRANS_ID is also unique.

Tags:

2

Answers


  1. 0

    From (the docs):

    Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order.

    I had to guess at the structure a little but it was enough to reproduce what you are seeing:

    CREATE TABLE table1 (
    ACCTNO INT,
    STORE_ID INT,
    AR_TRANS_ID INT,
    TRAN_TYPE CHAR(1),
    ORIGINAL_AMOUNT DECIMAL(8,2),
    BALANCE DECIMAL(8,2),
    COMMENT VARCHAR(255)
);
INSERT INTO table1 VALUES (1, 1, 1, 'D', 900000, 900000, NULL);

CREATE TABLE table2 (
    ACCTNO INT,
    STORE_ID INT,
    CURRENT_CD_BALANCE DECIMAL(8,2)
);
INSERT INTO table2 VALUES (1, 1, 900000);

    By reversing the order of the tables in the join I observed the expected behaviour:

    UPDATE table2 alias2
JOIN table1 alias1
    ON alias1.STORE_ID = alias2.STORE_ID
    AND alias1.ACCTNO = alias2.ACCTNO
SET
    alias2.CURRENT_CD_BALANCE = alias2.CURRENT_CD_BALANCE - alias1.ORIGINAL_AMOUNT,
    alias1.COMMENT = concat('Sum of ',alias1.ORIGINAL_AMOUNT,' Cancelled'),
    alias1.BALANCE = 0,
    alias1.ORIGINAL_AMOUNT = 0,
    TRAN_TYPE = 'D' 
WHERE alias1.AR_TRANS_ID = value1;

    db<>fiddle

    Given the quote from the MySQL docs referenced above, I am not sure that I would be comfortable relying on it.

    I cannot be 100% sure what you are trying to achieve but it does appear to be the reversal of some kind of financial transaction and an update of a related summary. Normally transaction histories should be treated as immutable, and any changes have to be applied as a new transaction, not an amendment to an existing transaction.

    Login or Signup to reply.
  2. 0

    The assignment order in multiple-table UPDATE is indefinite, the assignment expressions from SET clause are executed in indefinite ordering.

    If you want to perform this operation deterministically then you must use 2 copies of source tables. 1st one is updated, and another one provides the data for updating. In most cases the data for updating can be obtained in the subquery which is more optimal that 2 separate tables usage.

    I.e. instead of non-deterministic

    UPDATE t1
  JOIN t2 ON {join expression}
SET t1.col1 = expression(t2.col1),   -- imagine that there is
    t2.col2 = expression(t1.col2)    -- a cross-assignment there

    you’d use

    UPDATE t1
  JOIN t2 ON {join expression}
  JOIN ( SELECT t1.id id1, t2.id id2,             -- are used for to identify updated rows
                expression(t2.col1) new_t1_col1,  -- the values which will be
                expression(t1.col2) new_t2_col2   -- used for data updating
         FROM t1
         JOIN t2 ON {join expression}
        ) new_data ON t1.id = new_data.id1 AND t2.id = new_data.id2
SET t1.col1 = new_data.t1_col1,
    t2.col2 = new_data.t2_col2;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search