INSERT... ON DUPLICATE UPDATE if multiple values exist

Question

While executing an INSERT statement with many values, I want to update duplicate entries that contain the same data within two or more values.

After some research ON DUPLICATE KEY UPDATE seems to be the way to go. However I am struggling actually implementing it, even after reading numerous similar threads.

Am I right in this assumption? What's the best way to go about replacing data if the values are the same in two or more rows?

Steps that I have taken:

-Created my database

CREATE TABLE IF NOT EXISTS installs (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  username varchar(255)    NOT NULL,
  userid INT UNSIGNED NOT NULL DEFAULT '0',
  email varchar(255)    NOT NULL,
  addontitle varchar(255)    NOT NULL,
  addonversion varchar(255)    NOT NULL,
  bburl varchar(255)    NOT NULL,
  bbtitle varchar(255)    NOT NULL,
  webmasteremail varchar(255)    NOT NULL,
  cookie varchar(255)    NOT NULL,
  dateline INT UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (id),
  KEY userid (userid)
)

-Inserted Data via PHP script:

INSERT INTO installs (
      username,userid,email,addontitle,addonversion,bburl,bbtitle,webmasteremail,cookie,dateline
    ) VALUES (
      '$username','$userid','$email','$addontitle','$addonversion','$bburl','$bbtitle','$webmasteremail','$cookie',NOW()
    )
    ON DUPLICATE KEY UPDATE
    addontitle = VALUES($addontitle)

This doesn't quite get me the results I need though.

So as an example here are a few submitted rows:

1 - Bob - 25 - bob@test.com - Addon 1 - 1.0 - test.com - Test Site - admin@test.com - 13 - DATE
2 - Bob - 25 - bob@test.com - Addon 2 - 1.0 - test.com - Test Site - admin@test.com - 13 - DATE
3 - Bob - 25 - bob@test.com - Addon 2 - 1.2 - site.com - Some Site - admin@test.com - 13 - DATE

If an insert were to contain the following values, I'd like to be able to UPDATE row 1 since $bburl and $addontitle match.

4 - Bob - 25 - bob@test.com - Addon 1 - 2.0 - test.com - Test Site - admin@test.com - 13 - DATE

Then the table should read:

1 - Bob - 25 - bob@test.com - Addon 1 - 2.0 - test.com - Test Site - admin@test.com - 13 - NEWDATE
2 - Bob - 25 - bob@test.com - Addon 2 - 1.0 - test.com - Test Site - admin@test.com - 13 - DATE
3 - Bob - 25 - bob@test.com - Addon 2 - 1.2 - site.com - Some Site - admin@test.com - 13 - DATE

Is this type of operation possible to achieve using ON DUPLICATE KEY UPDATE? Is there a better method?

i have the feeling i have seen this code today before https://stackoverflow.com/questions/62244769/sql-preventing-duplicate-entries-if-2-values-exist. this code is **vulnerable to sql injection** please read https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php further you need constraints like **UNIQUE** to trigger teh ON DUPICATE — nbk, Jun 07 '20 at 16:20
I feel I've seen this response a few times, I'm not there, I'll get there. The question presented is what I am focused on ATM. — DrCustUmz, Jun 07 '20 at 16:25

score 0 · Answer 1 · answered Jun 07 '20 at 17:16

In order to use ON DUPLICATE KEY UPDATE you must create an index to enforce uniqueness across the two columns bburl, addontitle for MySQL to be able to detect duplication. This will create the index with an identifier called idx_uniq_bburl_addontitle `

Before you attempt this, you must ensure that you do not already have more than one occurrence of the combination of bburl, addontitle anywhere in your table. You can verify that with:

-- Check for non-unique rows
SELECT bburl, addontitle FROM installs GROUP BY bburl, addontitle HAVING COUNT(*) > 1

If that returns no rows, you can successfully create the multi-column index.

-- Create a multi-column unique index
ALTER TABLE `installs` ADD UNIQUE INDEX `idx_uniq_bburl_addontitle` (`bburl`, `addontitle`);

After the index has been created, you can use the following code which will update the addonversion if the DUPLICATE KEY is triggered.

INSERT INTO installs 
  (username,userid,email,addontitle,addonversion,bburl,bbtitle,webmasteremail,cookie,dateline)
  VALUES ('$username','$userid','$email','$addontitle','$addonversion','$bburl','$bbtitle','$webmasteremail','$cookie',NOW())
  ON DUPLICATE KEY UPDATE addonversion = '$addonversion';

To update more than one value for the row:

ON DUPLICATE KEY UPDATE addonversion = '$addonversion', bbtitle = '$bbtitle';

Please be advised that your use of variables like $bburl in the SQL statement suggest you are not using prepare()/execute() with PDO or MySQLi in your code. I would recommend reviewing How can I prevent SQL injection in PHP for suggestions on how to improve the situation.

INSERT... ON DUPLICATE UPDATE if multiple values exist

1 Answers1