'Conditional table updates

Consider the following table.

myTable
+----+-----------+------------------------------------+
| Id | responseA |             responseB              |
+----+-----------+------------------------------------+
|  1 |           | {"foo":"bar","lvl2":{"key":"val"}} |
+----+-----------+------------------------------------+

where:

Id, INT (11) PRIMARY
responseA, TEXT utf8_unicode_ci
responseB, TEXT utf8_unicode_ci

Let's say that I want to conditionally update the table with some outside data. The conditions are:

• if there's nothing in responseA, populate it with the outside data, otherwise

• if there is something in responseA, leave it as it is, and populate responseB with the outside data

I was pretty much convinced that I could just do this to get what I want:

UPDATE myTable
SET
    responseA = IF(TRIM(responseA) = '','foo',TRIM(responseA)),
    responseB = IF(TRIM(responseA) != '','foo',TRIM(responseB))
WHERE Id = 1

However, this updates both responseA and responseB to the same value - foo, making the table:

myTable
+----+-----------+-----------+
| Id | responseA | responseB |
+----+-----------+-----------+
|  1 |    foo    |    foo    |
+----+-----------+-----------+

I was expecting my table to look like this after the update:

myTable
+----+-----------+------------------------------------+
| Id | responseA |             responseB              |
+----+-----------+------------------------------------+
|  1 |    foo    | {"foo":"bar","lvl2":{"key":"val"}} |
+----+-----------+------------------------------------+

What am I misunderstanding, and how can I achieve this conditional update? Do the updates happen sequentially? If so, I guess that would explain why both of the fields are updated.

mysqlmariadb


Solution 1:[1]

here your changed query

UPDATE myTable
SET
    responseB = IF(TRIM(responseA) != '','foo',TRIM(responseB)),
    responseA = IF(TRIM(responseA) = '','foo',TRIM(responseA))
WHERE Id = 1

Solution 2:[2]

UPDATE TABLE
SET responseA = CASE WHEN responseA IS NULL
                     THEN @data
                     ELSE responseA
                     END,
    responseB = CASE WHEN responseA IS NULL
                     THEN responseB 
                     ELSE @data
                     END
;

Solution 3:[3]

It seems the value of responseA is changed before the IF() for responseB is evaluated.

One possible solution is to do a simple UPDATE:

UPDATE mytable SET responseA = ? WHERE id = 1

Then adjust the columns in a trigger, where you have access to both the original and the new value of the columns:

CREATE TRIGGER t BEFORE UPDATE ON mytable
FOR EACH ROW BEGIN
  IF TRIM(OLD.responseA) != '' THEN
    SET NEW.responseB = NEW.responseA;
    SET NEW.responseA = OLD.responseA;
  END IF;
END

(I have not tested this.)

I am also assuming that your test for '' (empty string) instead of NULL is deliberate, and that you know that NULL is not the same as ''.

Solution 4:[4]

The key point in the UPDATE statement is that you should update first the column responseB, so that column responseA retains its original value which can be checked again when you try to update it:

UPDATE myTable
SET responseB = CASE WHEN TRIM(responseA) = '' THEN responseB ELSE 'foo' END,
    responseA = CASE WHEN TRIM(responseA) = '' THEN 'foo' ELSE responseA END
WHERE Id = 1;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Bernd Buffen
Solution 2 Akina
Solution 3 Bill Karwin
Solution 4 forpas

Related Questions