Samuel Robert Samuel Robert - 2 months ago 8
JSON Question

How to nullify columns which are not updated with on duplicate key update in mysql

I'm working on a query that has to deal with insert records and update the column values if primary key is duplicate.

In my case column name and values are coming as JSON object like below

{
"col1": "val1",
"col2": "val2",
.....
}


My table has 5 columns and in the JSON object I may have only 2 or 3 columns out of 5.

Now How do I nullify the columns which are not updated with the query?

This is what I've tried thus far.

$columns = implode(', ', array_keys($requirement));
$values = array_values($requirement);
$placeholders = implode(', ', array_fill(0, count($values), '?'));

$updatedReq = $requirement;
unset($updatedReq['requirement_id']);
unset($updatedReq['buyer_id']);

$updated = array_keys($updatedReq);
array_walk($updated, function(&$value, $key) { $value .= '= ?'; });
$updatedPlaceholders = implode(', ', $updated);
$updatedVal = array_values($updatedReq);

$finalVal = array_merge($values, $updatedVal);

$sql = "INSERT INTO requirements ($columns) VALUES ($placeholders) ".
"ON DUPLICATE KEY UPDATE $updatedPlaceholders";
$db->query($sql);
$db->executeWithArray($finalVal);


$requirement is an associative array with keys as column names and values are the values to be updated in the table

Table Structure

CREATE TABLE requirement(
id integer PRIMARY KEY AUTO_INCREMENT,
col1 varchar(20) DEFAULT NULL,
col2 varchar(20) DEFAULT NULL,
col3 varchar(20) DEFAULT NULL,
col4 varchar(20) DEFAULT NULL,
col5 varchar(20) DEFAULT NULL,
created datetime DEFAULT CURRENT_TIMESTAMP,
updated datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)


NOTE: REPLACE INTO is not an option because one of the columns have a date value which shouldn't not be modified while updating the value.

Answer

Extend your ON DUPLICATE KEY to include all the columns you want to affect, using the VALUES() function like so:

ON DUPLICATE KEY UPDATE
    `val1` = VALUES(`val1`),
    `val2` = VALUES(`val2`),
    `val3` = VALUES(`val3`),
    `val4` = VALUES(`val4`),
    `val5` = VALUES(`val5`)

If your INSERT did not pass a value for a given column, then VALUES(`unpassed_column`) will yield NULL and this will be the newly saved value for that row.