0

Consider I have two tables, t1 and t2. t1 has two columns: c1 and c2. t2 has two columns: c1 and c3. We insert values in both tables:

CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 int, c3 int);

INSERT INTO t1 VALUES (10,10), (20,20);
INSERT INTO t2 VALUES (10,10), (20,20);

Updating t1 with this query:

update t1 
set c2 = c2 + 10,
    c2 = c2 + 20

will yield the next result: c2 column will respectively be equal to 40,50. This means, the first update was evaluated and then the next update used the first update value, so it's like using c2 = c2 + 10 + 20.

Considering I need to update t1 again, but this time, joining with t2 table, using this query:

update t1 left join t2 using (c1)
set c2 = c2+10,
    c2 = c2+20;

This query will yield the next result: c2 column will respectively be equal to 60,70, which means, the first update was ignored, and only the c2 = c2+20 was being evaluated at the end. I was expecting the results to be 70,80.

Here's a working example

I know that I didn't use t2 in the join, but in my real example I need to use t2 and I need to update same column twice, using the first update value.

Why does the same update with join yield different result, and is it possible to make it work without splitting the update into two separate updates?

EDIT: I've been told that the same code used to work on MariaDB 10.1, but after upgrading to 10.3 it failed. (Unfortunately, I can't find an online MariaDB 10.1 fiddle to test it myself)

Paul Karam
  • 4,052
  • 8
  • 30
  • 53
  • 3
    "I need to update the same column twice". I've done a lot of work with databases and I've never had to do that. I think there are probably better (or at least alternative) solutions for what you want to do. Note: I would expect your query to either return an error or to only use one of the expressions for the `update`. – Gordon Linoff Nov 18 '20 at 12:16
  • @GordonLinoff Splitting the updates in two consecutive ones is an option. This is not my code. I just identified the problem for a friend and asked about it to be able to reply with some insights from more experienced users on SO. It's not throwing an error, in fact, it's just using the second update. It's weird for me why would it work without a join, but with a join it only uses one of the `set` – Paul Karam Nov 18 '20 at 12:20
  • 2
    . . In the SQL standard, a `SET` uses values from the "before" table on the right hand side. No accumulation is possible. MySQL/MariaDB do not implement this functionality, but I certainly would not depend on such bespoke behavior (which is barely documented). – Gordon Linoff Nov 18 '20 at 12:23
  • In MySql the behavior you expect is consistent: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=dadc55747c741483410a32fc837b999b – forpas Nov 19 '20 at 10:52
  • @forpas It seems that it's something to do between MariaDB 10.1 and 10.3. – Paul Karam Nov 19 '20 at 16:50

0 Answers0