CODEWITHSUNDEEP
mysqlsqlstored-procedures
optional_gun
optional_gun

Reputation: 15

MYSQL Stored Procedure - Update multiple columns if certain conditions are met

My MySQL is fairly limited, and I've searched around a lot but I want to confirm what I've done so far.

I'm trying to write a stored procedure in MySQL that takes in 5 parameters. 4 parameters will contain values for columns that will be used to update, and a 5th value that will be used to select the rows to update.

parameter names: p_name1, p_name2, p_name3, p_name4, p_parentId column names: NAME_1, NAME_2, NAME_3, NAME_4, PARENT_ID

If there is a value set for the p_name1, update the column NAME_1 with the parameter value where the PARENT_ID for that row = p_parentId AND p_name1 isn't null. Do this for all columns. In some instances, only a couple of columns may need to be updated, so nothing will be passed in for the other columns. This is what I have come up with so far:

BEGIN
    UPDATE `NAMES_TABLE` SET `NAME_1` = p_name1 WHERE `PARENT_ID` = p_parentId AND p_name1 IS NOT NULL;
    UPDATE `NAMES_TABLE` SET `NAME_2` = p_name2 WHERE `PARENT_ID` = p_parentId AND p_name2 IS NOT NULL;
    UPDATE `NAMES_TABLE` SET `NAME_3` = p_name3 WHERE `PARENT_ID` = p_parentId AND p_name3 IS NOT NULL;
    UPDATE `NAMES_TABLE` SET `NAME_4` = p_name4 WHERE `PARENT_ID` = p_parentId AND p_name4 IS NOT NULL;
END

However, there will also be instances where the column will need to be set to null. Maybe for this I can use some sort of If statement for updates that says if parameter is set to a keyword (i.e. "null" as text), set column to null, else if parameter IS NOT NULL update with the parameter value, else don't update the column at all.

Is there a way to do this? Maybe with an if or a case statement within the update?

Thanks for any help you can give me.

EXAMPLE:

Original table with values

+------+-----------+-----------+-----------+----------+--------+
|   ID |  PARENT_ID| NAME_1    | NAME_2    | NAME_3   | NAME_4 | 
+------+-----------+-----------+-----------+----------+--------+
|  1   |  NULL     | APRIL     | AMBER     | ALFRED   | ALEX   |
|  2   | 1         | BOB       | BILL      | BURT     | BELINDA|
|  3   | 1         | BOB       | BILL      | BURT     | BELINDA|
|  4   | NULL      | CHARLES   | CASPER    | CONNOR   | CARL   |
+------+-----------+-----------+-----------+----------+--------+

pass in parameters:

p_name1 = JAMES, p_name2 = null, p_name3 = JEN, p_name4 = "null", p_parentId = 1

Result:

+------+-----------+-----------+-----------+----------+--------+
|   ID |  PARENT_ID| NAME_1    | NAME_2    | NAME_3   | NAME_4 | 
+------+-----------+-----------+-----------+----------+--------+
|  1   |  NULL     | APRIL     | AMBER     | ALFRED   | ALEX   |
|  2   | 1         | JAMES     | BILL      | JEN      | null   |
|  3   | 1         | JAMES     | BILL      | JEN      | null   |
|  4   | NULL      | CHARLES   | CASPER    | CONNOR   | CARL   |
+------+-----------+-----------+-----------+----------+--------+

Upvotes: 0

Views: 2695

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

If you need to handle a special "null" value, then CASE logic or multiple function calls are needed:

UPDATE NAMES_TABLE 
    SET Name_1 = (CASE WHEN @p_name1 = '<null>' THEN NULL
                       WHEN @p_name1 IS NOT NULL THEN @p_name1
                       ELSE Name_1
                  END),
        Name_2 = (CASE WHEN @p_name2 = '<null>' THEN NULL
                       WHEN @p_name2 IS NOT NULL THEN @p_name2
                       ELSE Name_2
                  END),
        Name_3 = (CASE WHEN @p_name3 = '<null>' THEN NULL
                       WHEN @p_name3 IS NOT NULL THEN @p_name3
                       ELSE Name_3
                  END),
        Name_4 = (CASE WHEN @p_name4 = '<null>' THEN NULL
                       WHEN @p_name4 IS NOT NULL THEN @p_name4
                       ELSE Name_4
                  END)
WHERE PARENT_ID = @p_parentId;

You can shorten this if you like to:

UPDATE NAMES_TABLE 
    SET Name_1 = NULLIF(COALESCE(@p_name1, Name_1), '<null>'),
        Name_2 = NULLIF(COALESCE(@p_name2, Name_2), '<null>'),
        Name_3 = NULLIF(COALESCE(@p_name3, Name_3), '<null>'),
        Name_4 = NULLIF(COALESCE(@p_name4, Name_4), '<null>')
WHERE PARENT_ID = @p_parentId;

I recommend the first version for someone newer to SQL. The logic is quite explicit.

Upvotes: 0

slaakso
slaakso

Reputation: 9050

Use ifnull-function:

BEGIN
  UPDATE NAMES_TABLE SET 
    NAME_1 = ifnull( p_name1, NAME_1),
    NAME_2 = ifnull( p_name2, NAME_2),
    NAME_3 = ifnull( p_name3, NAME_3),
    NAME_4 = ifnull( p_name4, NAME_4)
  WHERE PARENT_ID = p_parentId;
END

Upvotes: 0

VB_isYoung
VB_isYoung

Reputation: 83

try this: 


DELIMITER //
CREATE PROCEDURE usp_name (IN @p_name1 varchar(20)) NULL, 
                            IN @p_name2 varchar(20) NULL, 
                            IN @p_name3 varchar(20) NULL, 
                            IN @p_name4 varchar(20) NULL, 
                            IN @p_parentId varchar(20) NULL)
AS
BEGIN


UPDATE NAMES_TABLE 
SET Name_1 = IFNULL(@p_name1, Name_1 ),
    Name_2 = IFNULL(@p_name2, Name_2 ),
    Name_3 = IFNULL(@p_name3, Name_3 ),
    Name_4 = IFNULL(@p_name4, Name_4 )
WHERE 'PARENT_ID' = @p_parentId;

END//

DELIMITER ;

Upvotes: 1

Related Questions