MySQL update with if evaluation in same table

Question

I need some help.

I got this table:

---------
ID | N1 |
---------
a    100
b    150
c    90
d    240
--------

Given 2 IDs (for example a,b) I need to do that:

If (a.n1 > b.n1) 
        
       a.n1 = b.n1
    
else 
        b.n1=a.n1;

How can I do it on MySQL?

question from:https://stackoverflow.com/questions/66066075/mysql-update-with-if-evaluation-in-same-table

Answer 1

If you're wanting to update a table in this manner you can use a Stored Procedure:

CREATE TABLE T (ID VARCHAR(5), N1 INT);
DELIMITER $$
CREATE PROCEDURE check_vals (IN a VARCHAR(5), IN b VARCHAR(5)) BEGIN 
SET @a = (SELECT N1 FROM T WHERE ID = a); 
SET @b = (SELECT N1 FROM T WHERE ID = b); 
IF @a > @b THEN UPDATE T SET N1 = @b WHERE ID = a; 
ELSE UPDATE T SET N1 = @a WHERE ID = b; 
END IF; 
END $$
DELIMITER ;

Then Just use the procedure like this:

CALL check_vals('a','b');
SELECT * FROM T;

Returns:

+----+-----+
| ID | N1  |
+----+-----+
| a  | 100 |
| b  | 100 |
| c  |  90 |
| d  | 240 |
+----+-----+

Otherwise, if your goal is to only manipulate the data in a SELECT result, then I would go with P.Salmon's answer.