On duplicate key update multiple values MYSQL

Question

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?

Please explain more detailed. both of your queries require the same amount of information, while the second is just "invalid"...? - You know all the columns in order to call INSERT INTO - wheres the problem, repeating them in the ON DUPLICATE KEY Statement? — dognose
– dognose, Commented Oct 19, 2018 at 21:31
@dognose It ain't really a problem, I was just curious to see if there is a more elegant way of writing the query, I changed the question to explain better why. Thanks. — Eduardo Ponce de Leon
– Eduardo Ponce de Leon, Commented Oct 19, 2018 at 22:01
You can use the REPLACE query instead of INSERT, it allows that kind of syntax. However, it has the misfeature that instead of updating rows, it deletes them and recreates them. — Barmar
– Barmar, Commented Oct 19, 2018 at 22:30

dognose · Accepted Answer · 2018-10-19 23:22:45Z

3

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)

edited Oct 19, 2018 at 23:22

answered Oct 19, 2018 at 23:10

dognose

21k9 gold badges67 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eduardo Ponce de Leon Over a year ago

Wow, array_walk() I learned something new today! Amazing, thank you very much.

Collectives™ on Stack Overflow

On duplicate key update multiple values MYSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related