Question 20344 in Mysql Question posted in
The official MySQL documentation can be found here.

Create a stored procedure in MySQL to update a table with a flexible/dynamic set of columns?

I can easily write a stored procedure that would take inputs and use them to update a table:

CREATE PROCEDURE update_table (
    IN parameter1 datatype1,
    IN parameter2 datatype2,
    ...,
    IN parameterN datatypeN
)
BEGIN
    UPDATE
        table_name
    SET
        column1 = parameter1,
        column2 = parameter2,
        ...,
        columnN = parameterN
    WHERE
        condition;
END;

This would work if I always wanted to update the same exact same predetermined set of columns every time the proc is called.

But, how could I create a proc that allowed me the flexibility to choose which columns to update? (I.e., update a different set of columns each time the proc is used, based on the parameters passed).

I thought about allowing the caller to set some parameters to NULL, and then only updating the columns that correspond to the non-NULL parameters, but that doesn’t work, because the caller would never be able to set a column to NULL if they wanted to.

I could also use some dynamic SQL, but that opens up the possibility of a SQL injection attack.

It seems like this is a common requirement that probably has a time-tested design pattern.

Tags:

2

Answers


  1. 0

    More detailed example from my comment, you can change it to update the columns every time but but only update values from parameters if they are not null:

    (Updated to use Bill Karwin’s Suggestion from comments to use COALESCE instead of CASE NOT NULL check)

    UPDATE
        table_name
    SET
        column1 = COALESCE(parameter1, column1),
        column2 = COALESCE(parameter2, column2),
        ...,
        columnN = COALESCE(parameterN, columnN)
    WHERE
        condition;

    Not nearly as clean and can probably be tweaked to be better than what I have but the logic will work and still be better than dynamic SQL.

    You could create a function or something to call for the case statement to make it more readable and easier to maintain.

    Updated to give you the ability to set a value to NULL,
    but you have to be able to do something on the app/code side to set the parameter value to something you would never pass to update the column to

    I used: ‘@@@@@@@@@@’ string but this could be anything

    UPDATE
        table_name
    SET
        column1 = CASE 
            WHEN paramater1 = '@@@@@@@@@@' THEN NULL
            WHEN paramater1 IS NOT NULL AND paramater1 != '@@@@@@@@@@' THEN paramater1
            WHEN paramater IS NULL THEN column1
        END
        .. repeat FOR each one
    WHERE
        condition;

    This will update every column every time but if the parameter values are NULL then it will just update the column to the existing value in the column (thus not changing it), and if the parameter is not null, it will use that value to update the column.

    As long as you dont have triggers on the columns this will work.

    Login or Signup to reply.
  2. 0

    You can’t have unlimited flexibility at the same time as stability.

    In Brad’s answer, the data value to set is overloaded as a control variable as well, determining whether to set the value or not.

    If you must allow users to set any value including NULL, then you need to add more boolean columns to control which columns to set. This separates the logic from the data values to set.

    CREATE PROCEDURE update_table (
    IN set_parameter1 BOOL,
    IN parameter1 datatype1,
    IN set_parameter2 BOOL,
    IN parameter2 datatype2,
    ...,
    IN set_parameterN BOOL,
    IN parameterN datatypeN
)
BEGIN
    UPDATE
        table_name
    SET
        column1 = IF(set_parameter1, parameter1, column1),
        column2 = IF(set_parameter2, parameter2, column2),
        ...,
        columnN = IF(set_parameterN, parameterN, columnN)
    WHERE
        condition;
END

    Another approach would be to use JSON. I am reluctant to talk about this, because I see JSON so frequently abused in Stack Overflow questions. I don’t want to risk you thinking that JSON is a good way to store data.

    JSON objects can have a NULL value for a given key, but also the key may be omitted from the JSON object. So this can be used to indicate which columns to set and which to leave as is.

    CREATE PROCEDURE update_table (
    IN parameters JSON
)
BEGIN
    UPDATE
        table_name
    SET
        column1 = IF(JSON_CONTAINS_PATH(parameters, 'one', '$.column1'), parameters->>'$.column1', column1),
        column2 = IF(JSON_CONTAINS_PATH(parameters, 'one', '$.column2'), parameters->>'$.column2', column2),
        ...,
        columnN = IF(JSON_CONTAINS_PATH(parameters, 'one', '$.columnN'), parameters->>'$.columnN', columnN)
    WHERE
        condition;
END

    But this requires your users to understand how to format the parameters argument in JSON.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search