Insert on duplicate update

Question

I have MySQL table and I need to make query INSERT ON DUPLICATE UPDATE. "id" is primary key, I dont need it for now.

I need to insert values "id2", "name", "city" into the table. If there is some row with the same values in "id2" and "name", then just update "city". Please where am I wrong ?

$query = "INSERT INTO 
             `table` 
               (id2, name, city) 
          VALUES 
               ( '$new_id2', '$new_name', '$new_city') 
          ON DUPLICATE KEY UPDATE 
               city='$new_city' 
          WHERE 
              `id2`='$new_id2' AND `name`='$new_name'
          ";

Your syntax is incorrect. An `INSERT` statement never has a `WHERE` clause. It should end after the `ON DUPLICATE KEY UPDATE` — Michael Berkowski, Dec 12 '16 at 14:59
You said `If there is some row with the same values in "id2" and "name"` but you ony have `id2` as a primary key. Is `name` intended to be a unique value as well? — Michael Berkowski, Dec 12 '16 at 15:00
Primary key is ID. It is autoincrement... I can not get ID. I just wanna insert in this table values... If there is some row with "id2" and "name" values, then only update other values - "city" for example.... — Patrik, Dec 12 '16 at 15:03
Oh, sorry I misunderstood that you have an auto-increment `id` separate from `id2`. You will need a unique index on the other columns. I can post an answer below. — Michael Berkowski, Dec 12 '16 at 15:05
I don't have unique index. Unique is just combination id2 + name. I can not change keys in this table. :-( — Patrik, Dec 12 '16 at 15:07
If you cannot change the keys to create a new index, then you cannot use `ON DUPLICATE KEY UPDATE`. Note that creating a new index does not require you to eliminate your existing primary key `id`. Do you not have the ability to run `ALTER TABLE`? — Michael Berkowski, Dec 12 '16 at 15:09
I can make some value unique. But it is not unique. Id2 and name are in the table many times. What can not be many times in table, is combination id2 + name. Is there some solution for me ? — Patrik, Dec 12 '16 at 15:12
Yes. A key can have 2 columns. If you can create a new index, there is a solution as a multi-column unique index. — Michael Berkowski, Dec 12 '16 at 15:19
"ALTER TABLE `table` ADD INDEX(`id2`); and "ALTER TABLE `table` ADD INDEX(`name`); ? Yes, I can. — Patrik, Dec 12 '16 at 15:22

score 4 · Accepted Answer · edited May 23 '17 at 10:30

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

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

-- Check for non-unique rows
SELECT id2, name FROM table GROUP BY id2, name HAVING COUNT(*) > 1

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

-- Create a multi-column unique index
ALTER TABLE `table` ADD UNIQUE INDEX `idx_uniq_id2_name` (`id2`, `name`);

After the index has been created, you must correct the syntax of your INSERT statement such that it has no WHERE clause (which is never allowed for an INSERT).

INSERT INTO `table` 
  (id2, name, city)  
  VALUES ('$id2', '$name', '$new_city')
  ON DUPLICATE KEY UPDATE city = '$new_city';

Please be advised that your use of variables like $new_city 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

1 Answers1