I encountered an unexpected behavior in MariaDB when performing an UPDATE statement with multiple SET clauses. Here's the setup:

Table Creation and Initial Data

CREATE OR REPLACE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2),
    discount_price DECIMAL(10,2)
);

INSERT INTO products (name, category, price, discount_price) VALUES
('Laptop', 'electronics', 1000.00, 900.00),
('Smartphone', 'electronics', 800.00, 720.00),
('Tablet', 'electronics', 500.00, 450.00),
('Headphones', 'accessories', 200.00, 180.00);

Case 1: Expected Behavior

When I run the following query:

UPDATE products 
SET price = price * 1.10, 
    discount_price = price * 0.90 
WHERE category = 'electronics';

The SET clauses evaluate sequentially, as expected: price is updated first (price = price * 1.10). Then discount_price is calculated based on the updated value of price. This produces the correct results.

Case 2: Unexpected Behavior

However, when I use a subquery in the WHERE clause:

UPDATE products 
SET price = price * 1.10, 
    discount_price = price * 0.90 
WHERE id IN (SELECT id FROM products WHERE category = 'electronics');

The behavior changes: it seems like the SET clauses are evaluated simultaneously, not sequentially. As a result:

The discount_price is calculated based on the old value of price, instead of the updated value. This leads to incorrect results for discount_price.

My Understanding

From my knowledge of MariaDB's behavior, multiple SET clauses in an UPDATE statement should be evaluated sequentially from left to right. This works as expected when using a simple condition in the WHERE clause but behaves differently when a subquery is involved.

Question

Is this behavior a bug in MariaDB? Or is there some underlying reason why the presence of a subquery in the WHERE clause causes the SET clauses to evaluate simultaneously? How can I ensure sequential evaluation in such cases?

Environment

MariaDB version: 10.6.20 (AWS RDS Mariadb)

I encountered an unexpected behavior in MariaDB when performing an UPDATE statement with multiple SET clauses. Here's the setup:

Table Creation and Initial Data

CREATE OR REPLACE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2),
    discount_price DECIMAL(10,2)
);

INSERT INTO products (name, category, price, discount_price) VALUES
('Laptop', 'electronics', 1000.00, 900.00),
('Smartphone', 'electronics', 800.00, 720.00),
('Tablet', 'electronics', 500.00, 450.00),
('Headphones', 'accessories', 200.00, 180.00);

Case 1: Expected Behavior

When I run the following query:

UPDATE products 
SET price = price * 1.10, 
    discount_price = price * 0.90 
WHERE category = 'electronics';

The SET clauses evaluate sequentially, as expected: price is updated first (price = price * 1.10). Then discount_price is calculated based on the updated value of price. This produces the correct results.

Case 2: Unexpected Behavior

However, when I use a subquery in the WHERE clause:

UPDATE products 
SET price = price * 1.10, 
    discount_price = price * 0.90 
WHERE id IN (SELECT id FROM products WHERE category = 'electronics');

The behavior changes: it seems like the SET clauses are evaluated simultaneously, not sequentially. As a result:

The discount_price is calculated based on the old value of price, instead of the updated value. This leads to incorrect results for discount_price.

My Understanding

From my knowledge of MariaDB's behavior, multiple SET clauses in an UPDATE statement should be evaluated sequentially from left to right. This works as expected when using a simple condition in the WHERE clause but behaves differently when a subquery is involved.

Question

Is this behavior a bug in MariaDB? Or is there some underlying reason why the presence of a subquery in the WHERE clause causes the SET clauses to evaluate simultaneously? How can I ensure sequential evaluation in such cases?

Environment

MariaDB version: 10.6.20 (AWS RDS Mariadb)

• sql
• mariadb
• sql-update
• mariadb-10.6

• 1 I would guess the subquery is being treated like a join and so you aren't getting the single table update behavior – ysth Commented Mar 9 at 15:28
• 1 "..The SET clauses evaluate sequentially, as expected: price is updated first (price = price * 1.10). Then discount_price is calculated based on the updated value of price. This produces the correct results..." -- No, this is not a the expected result. The UPDATE should work on the image of the values before the query is run, not using intermediate values. – The Impaler Commented Mar 9 at 15:47
• 2 @OzzyBlack I think you are right. The MariaDB manual says "For single-table updates, assignments are evaluated in left-to-right order, while for multi-table updates, there is no guarantee of a particular order." -- However, this differs from normal SQL where all values are taken before any SET is applied. It can be handy to understand this non-standard behavior if later on we wanted to migrate to another db. – The Impaler Commented Mar 9 at 16:19
• 2 @TheImpaler That's presumably why there's a SQL Mode that controls it -- you can use this to get standard SQL behavior. – Barmar Commented Mar 9 at 16:27
• 4 So the only way to have a reproducible result in non-SQL mode would be to precompute the new values in a subselect to apply them with an UPDATE products JOIN (SELECT … newprice, … newdiscountprice FROM products) newprice ON newprice.id = products.id SET products.price = newprice.price etc. as in this fiddle? As the SELECT explicitely computes from old values. – Guillaume Outters Commented Mar 9 at 17:14

1 Answer 1

I personally like to solve this (also use it in fairly large queries just for readability) with user defined variables.
So partial results can be reused.

You can write the query like this:

SET @oldPrice=0; 
SET @newPrice=0;
UPDATE 
    `products`
SET
    `price` = IF(((@oldPrice:=`price`) OR TRUE) AND ((@newPrice:=(@oldPrice * 1.1)) OR TRUE), (SELECT @newPrice), (SELECT @newPrice)),
    `discount_price` = (@newPrice * 0.9)
WHERE 
    `id` IN (SELECT id FROM `products` WHERE `category`  = 'electronics');

Which results in:

The @oldPrice variable is not really needed here, but ok.

A fiddle https://dbfiddle.uk/30BeFMNE