Mysql Function Problem

Question

I am using a MySQL function to find out the max record in that table.

SQL data:

id_  index  Value1  Value2  Value 3  Max_idVal
1    'abc'    5       10     5        5
1    'abc'    0       12     4        5
1    'abc'    0       13     3        5
2    'abc'    4       9      10       8
2    'abc'    8       10     8        8
   

Max_idVal is the result returned by the MySQL function.

The idea is to go through each row and put the maximum value into the Max_idVal field for that row.

Here for ID_ 1 the Value1 is the largest value at '5'. That gets assigned to Max_idVal'. This works the same way for ID_` 2 and the rest of the rows.

SQL function:

 DELIMITER $$
    CREATE FUNCTION `fn_get_max` (
    _id INT,
    _index VARCHAR( 30 )
    ) RETURNS INT( 11 ) READS SQL DATA BEGIN DECLARE r INT;

   SELECT Max(value1 ) 
   INTO r
   FROM Table_name
   WHERE id = _id
   AND index = _index;

   RETURN r;

   END $$

SQL query:

  UPDATE table_name SET max_idval = fn_get_max('1','abc') WHERE id = '1'

My problem is that this returns NULL rows even though the ID matches a record.

What am I doing wrong?

Answer 1

Hi, there are a couple of issues I can see - firstly the word index is reserved so in your function you should use index. Secondly in the update statement provided your WHERE clause should be id_ = 1 not id = 1. I have recreated your table and function using the following code:

    DELIMITER $$
    DROP FUNCTION IF EXISTS `test`.`fn_get_max`$$

    CREATE DEFINER=`root`@`localhost` FUNCTION `fn_get_max2`(  _id INT,
    _index VARCHAR( 30 )) RETURNS INT(11)
    BEGIN
       DECLARE r INT;


       SELECT MAX(value1 ) 
       INTO r
       FROM test_table
       WHERE id_ = _id
       AND `INDEX` = _index;
       RETURN r;

    END$$

    DELIMITER ;

Then using the following update statement correctly updates max_idval

UPDATE test_table SET max_idval = fn_get_max2('1','abc') WHERE id_ = '1'

If this is just a once off update you could do it more efficiently with a temporary table (as MySQL doesn't allow tables to join to themselves in an update query):

CREATE TEMPORARY TABLE tmp (id_ INT, maxvalue1 INT);

INSERT INTO tmp (id_, maxvalue1)
SELECT id_, MAX(Value1) AS max_value1
FROM test_table
GROUP BY id_;


UPDATE test_table, tmp  SET test_table.Max_idVal = tmp.maxvalue1
WHERE test_table.id_ = tmp.id_

That should update all rows correctly

HTH