Menu
What are peoples' thoughts on the most performance efficient way to do the following query:
3 column table
if the combination of col_1 and col_2 values already exist UPDATE col_3
else INSERT new row
I assume i need some kind if UPDATE ON DUPLICATE KEY (which i've never used before), however I do not have a 'KEY' but instead a pair of two values (columns) to make a key...
941
ON DUPLICATE KEY UPDATE is a MariaDB/MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE. The row/s affected value is reported as 1 if a row is inserted, and 2 if a row is updated, unless the API's CLIENT_FOUND_ROWS flag is set.
You can define keys which allow duplicate values. However, do not allow duplicates on primary keys as the value of a record's primary key must be unique. When you use duplicate keys, be aware that there is a limit on the number of times you can specify the same value for an individual key.
The Insert on Duplicate Key Update statement is the extension of the INSERT statement in MySQL. When we specify the ON DUPLICATE KEY UPDATE clause in a SQL statement and a row would cause duplicate error value in a UNIQUE or PRIMARY KEY index column, then updation of the existing row occurs.
Answer: No it is not possible. The wording Primary Key imply non duplicate values, it is by design ! You can have duplicate values in Non Primary Key, it is the only way.
You can create a PRIMARY or UNIQUE key out of multiple columns (called a composite key) in MySQL, which'll allow ON DUPLICATE KEY to work just fine.
// create a composite index
CREATE INDEX my_composite_index ON my_table (column1, column2);
// insert or update
INSERT INTO my_table (column1, column2) VALUES ('value1', 'value2') ON DUPLICATE KEY UPDATE column3=column3+1;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
