Using MIN() in SET statement MySQL

Question

I am using MySQL. Lets call a table that I have as Inventory which looks is below:

+----+--------+--------+-------------+----------+ | ID | Price1 | Price2 | TargetPrice | Quantity | +----+--------+--------+-------------+----------+ | 1 | 12 | 1 | | 0 | | 2 | 3 | 3 | 3 | 2 | | 3 | | 4 | | 0 | | 4 | 2 | 2 | 2 | 2 | | 5 | 5 | 45 | 5 | 1 | +----+--------+--------+-------------+----------+

Now, I need to update the TargetPrice to minimum of Price1 and Price2 for any row whose Quantity is 0

I have tried:

UPDATE Inventory SET TargetPrice= MIN(Price1,Price2) WHERE Quantity >0

However, MySQL complains about the usage of MIN() function. I know it is expecting MIN() to work on the data contained inside column, rather than taking MIN() of two columns of a specified row.

Anyway to achieve this other than cursors?

EDIT: Price1 and Price2 can be null or 0 and in all these cases, it should be treated as infinity so that the other price gets to be minimum when compared against it.

Answer 1

Use LEAST instead of MIN:

UPDATE Inventory 
SET TargetPrice = LEAST(Price1,Price2) 
WHERE Quantity = 0

MIN is an aggregate function operating on a rowset, whereas LEAST operates on the list of arguments passed.

EDIT:

UPDATE Inventory 
SET TargetPrice = LEAST(COALESCE(Price1, Price2), COALESCE(Price2, Price1))  
WHERE Quantity = 0

You can use COALESCE to handle NULL values.

EDIT2:

You can use NULLIF to handle 0 values:

UPDATE Inventory 
SET TargetPrice = LEAST(COALESCE(NULLIF(Price1,0), Price2), 
                        COALESCE(NULLIF(Price2,0), Price1))  
WHERE Quantity = 0