Unexpected Query Result Based on Old Column Value in MySQL

MySQL

I need to update a column and based on the old value, update a second column.

After googling I came up with THIS (*nr 4) solution: copy the table to have the old value at hand.

The table looks like

   id | upd | a1 | a2 .. | b1 | b2 | b3 |
   1  |     | 1  | 1     | 1  | 1  | 1  |

My query looks like:

UPDATE table t1, table t2 SET
    t1.b1  = GREATEST( 5, t1.a1),
    t1.upd = IF(t1.b1 <> t2.b1 ,1 , t1.upd)
WHERE t1.id = t2.id

Based on this query I would expect the table to look like this:

   id | upd | a1 | a2 .. | b1 | b2 | b3 |
   1  | 1*  | 1  | 1     | 5* | 1  | 1  |
*= updated

Only, it isn't. It executes the first line correctly (updating b1 to 5), but not the second one, setting the upd field.

The mysql manual states:

The second assignment in the following statement sets col2 to the current (updated) col1 value,
not the original col1 value. The result is that col1 and col2 have the same value.
 This behavior differs from standard SQL. 
UPDATE t1 SET col1 = col1 + 1, col2 = col1;

So I would have expected the second line (update upd) to work as well.

If I add (as a test) these two lines

t1.b2=t1.b1,
t1.b3=t2.b1,

before the t1.upd = line, b2 and b3 still show 1, where I expected b2 to be 5.

Where am I going wrong in my logic (and understanding of MySQL :-)?

NOTE Sorry, I forget to mention: I connot use Triggers. Otherwis it would have been less difficult

Best Answer

I would try this one:

UPDATE 
    table t1 
SET
    upd = 1, 
    b1 = GREATEST(5, a1) 
WHERE
    b1 <> GREATEST(5, a1) ;

No need for "copying" the table or using the (not 100% sure) assumption that MySQL updates the columns in a specific order.

The answers in your link are about a much more difficult to do (in MySQL) operation, i.e. swapping the values of 2 columns.

If you want to update multiple columns and set the upd=1 column if at least one of the other columns is updated, you can use a simple OR in the WHERE clause:

UPDATE 
    table t1 
SET
    upd = 1, 
    b1 = GREATEST(5, a1),
    b2 = GREATEST(7, a2),
    b3 = <whatever> 
    --- 
WHERE
   ( b1 <> GREATEST(5, a1)
  OR b2 <> GREATEST(7, a2)
  OR b3 <> <whatever> 
  ---
   ) ;

Related Solutions

MySQL Query: Delete from ‘table2’ where column = ‘value’ IF column in ‘table1’ = ‘value’

Your statement seems to be correct. It could also be written like this:

DELETE ii
FROM inventoryitems AS ii 
WHERE ii.itemid = 2340000
  AND EXISTS  
    ( SELECT * 
      FROM characters AS c
      WHERE c.characterid = ii.characterid
        AND EXISTS
            ( SELECT *
              FROM accounts AS a
              WHERE a.id = c.accountid 
                AND a.banned = 1
            )
    ) ;

One thing that may be causing this is if you have a character related to many accounts and one of them has banned = 1 while the other have banned = 0. I assume you want the deletion to happen (not with just one but) only if all the related accounts have banned = 1. We can modify the above code to:

DELETE ii
FROM inventoryitems AS ii 
WHERE ii.itemid = 2340000
  AND EXISTS  
    ( SELECT * 
      FROM characters AS c
      WHERE c.characterid = ii.characterid
        AND EXISTS
            ( SELECT *
              FROM accounts AS a
              WHERE a.id = c.accountid 
                AND a.banned = 1
            )
        AND NOT EXISTS
            ( SELECT *
              FROM accounts AS a
              WHERE a.id = c.accountid 
                AND a.banned = 0
            )
    ) ;

or simpler to:

DELETE ii
FROM inventoryitems AS ii 
WHERE ii.itemid = 2340000
  AND EXISTS  
    ( SELECT * 
      FROM characters AS c
        JOIN accounts AS a
          ON a.id = c.accountid
      WHERE c.characterid = ii.characterid 
      HAVING MIN(a.banned) = 1
    ) ;

After the clarifications, all the above are void. The problem was that characters table does not have characterid column but only id. So the statement used by the OP was translated/parsed as:

DELETE FROM inventoryitems
WHERE characterid IN 
    (SELECT inventoryitems.characterid         -- notice this
     from characters
     WHERE accountid IN 
        (SELECT id 
         from accounts 
         WHERE banned = '1'
        )
    ) 
  AND itemid = '2340000';

which makes the subquery uncorrelated and means "delete all rows with itemid = 2340000" if there exists at least one row (any row, not necessarily related) in the accounts with banned=1"

That's one reason why it's good to always (*) write columns with their full name as tablename.columnname or tablealias.columnname (an error would have been thrown if you had done this and problem would have been solved faster.)

(*) Unless one wants this behaviour to occur, which is a rather extreme case.

Mysql – Store equations/formula in thesql db table

You can try to leverage dynamic SQL.

If you need to get a calculated value for an id

DELIMITER $$
CREATE PROCEDURE get_value(IN _id INT)
BEGIN
  SET @sql = NULL;

  SELECT CONCAT('SELECT ', formula, ' value FROM table1 WHERE id = ', 1)
    INTO @sql
    FROM table1
   WHERE id = 1;

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  SET @sql = NULL;
END$$
DELIMITER ;

Note: You can of course use OUT parameter instead of returning the resultset if you want to.

Sample usage:

CALL get_value(1);

Sample output:

|           VALUE |
|-----------------|
| 82.916129032258 |

Here is how a procedure might look like to get all values calculated by formulas

DELIMITER $$
CREATE PROCEDURE get_values()
BEGIN
  SET @sql = NULL;

  SELECT GROUP_CONCAT(CONCAT(
    'SELECT id, ', formula, ' value FROM table1 WHERE id = ', id)
    ORDER BY id SEPARATOR ' UNION ALL ')
    INTO @sql
    FROM table1;

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  SET @sql = NULL;
END$$
DELIMITER ;