CODEWITHSUNDEEP
mysqlon-duplicate-key
Eduardo Ponce de Leon
Eduardo Ponce de Leon

Reputation: 9696

On duplicate key update multiple values MYSQL

We have a dynamic query that inserts data from a CSV file, what we want is to update all the rows even if there are duplicates.

We know that this works:

INSERT INTO TABLE (ID1,ID2,ID3,PAYDATE,PRICE,CURRENCY)
VALUES ('75','2','16','2018-11-5','300','CAD'),
('75','2','17','2018-11-10','400','USD')
ON DUPLICATE KEY UPDATE 
ID1=VALUES(ID1),
ID2=VALUES(ID2),
ID3=VALUES(ID3),
PAYDATE=VALUES(PAYDATE),
PRICE=VALUES(PRICE),
CURRENCY=VALUES(CURRENCY)

But this can become complicated when creating a dynamic query, where we don't know the table structure before hand. So we are looking for a query similar to this:

INSERT INTO TABLE (ID1,ID2,ID3,PAYDATE,PRICE,CURRENCY) 
VALUES ('75','2','16','2018-11-5','300','CAD'),
('75','2','17','2018-11-10','400','USD')
ON DUPLICATE KEY UPDATE
(ID1,ID2,ID3,PAYDATE,PRICE,CURRENCY)
VALUES(ID1,ID2,ID3,PAYDATE,PRICE,CURRENCY)

This last one obviously doesn't work but it is easier for us to work with it because we already have the columns in a single string and the first query requires exploding the string into an array and execute a few nested loops.

//PHP
$cols = "ID1,ID2,ID3,PAYDATE,PRICE,CURRENCY";
$vals = "('75','2','16','2018-11-5','300','CAD'),
    ('75','2','17','2018-11-10','400','USD')";

$query = "INSERT INTO TABLE ($cols) 
    VALUES $vals
    ON DUPLICATE KEY UPDATE
    ($cols) VALUES($cols)";

Is there a similar way?

Upvotes: 1

Views: 2084

Answers (1)

dognose
dognose

Reputation: 20899

Your PHP Snippet makes your intention way clearer.

No, there is no way to handle this in the SQL-Query.

However, you can simply add "more magic" to the PHP-Script in order to generate the required query.

I.e. you are already creating the proper INSERT-Statement out of 2 arrays. So, all you need to do is to create the proper ON DUPLICATE KEY-Statement as well. :-)

array_walk, implode and explode are your friends here: 

$cols = "ID1,ID2,ID3,PAYDATE,PRICE,CURRENCY";
$vals = "('75','2','16','2018-11-5','300','CAD'),
    ('75','2','17','2018-11-10','400','USD')";

//Create a pseudoArray, with VALUES() Keyword
$pseudoArray = explode(",", $cols);
array_walk($pseudoArray, "wrapIt");

function wrapIt(&$item, $value){
   $item = $item . "=VALUES(" . $item . ")";
}

$query = "INSERT INTO TABLE ($cols) 
    VALUES $vals
    ON DUPLICATE KEY UPDATE
    " . implode(",", $pseudoArray);


echo $query;

will produce: 

INSERT INTO TABLE 
    (ID1,ID2,ID3,PAYDATE,PRICE,CURRENCY) 
VALUES 
    ('75','2','16','2018-11-5','300','CAD'), 
    ('75','2','17','2018-11-10','400','USD') 
ON DUPLICATE KEY UPDATE 
    ID1=VALUES(ID1), 
    ID2=VALUES(ID2), 
    ID3=VALUES(ID3), 
    PAYDATE=VALUES(PAYDATE), 
    PRICE=VALUES(PRICE), 
    CURRENCY=VALUES(CURRENCY)

which should be what is required. Modify wrapIt to exclude key/value pairs you don't want to update in case of an duplicate key. (If any)

Upvotes: 3

Related Questions