title: MYSQL Stored Procedure - Update multiple columns if certain conditions are met
body: <p>My MySQL is fairly limited, and I've searched around a lot but I want to confirm what I've done so far.</p>
  
  <p>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.</p>
  
  <p>parameter names: p_name1, p_name2, p_name3, p_name4, p_parentId
  column names: NAME_1, NAME_2, NAME_3, NAME_4, PARENT_ID</p>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>Is there a way to do this? Maybe with an if or a case statement within the update?</p>
  
  <p>Thanks for any help you can give me.</p>
  
  <p>EXAMPLE:</p>
  
  <p>Original table with values</p>
  
  <pre><code>+------+-----------+-----------+-----------+----------+--------+
  |   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   |
  +------+-----------+-----------+-----------+----------+--------+
  </code></pre>
  
  <p>pass in parameters:</p>
  
  <p>p_name1 = JAMES, p_name2 = null, p_name3 = JEN, p_name4 = "null", p_parentId = 1</p>
  
  <p>Result:</p>
  
  <pre><code>+------+-----------+-----------+-----------+----------+--------+
  |   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   |
  +------+-----------+-----------+-----------+----------+--------+
  </code></pre>