Mysql insert on duplicate key update with where alternative

Asked Jan 31 '18 at 11:49

Active Feb 17 '18 at 15:12

Viewed 445 times

I would like to know if there is a way to use insert into...on duplicate key without use as parameter the primary key of the table, for example I have the following table :

+----+------------+------+--------+
| id | product_id | year |  name  |
+----+------------+------+--------+
|  1 |          5 | 2018 | Sword  |
|  2 |          7 | 2018 | Shield |
+----+------------+------+--------+

I have an array for insert in that database (PHP - PDO).

This array have 3 items :

the sword
the shield
and the potion

For example, with id as primary key, I would like to know if its possible to perform something like INSERT ... ON DUPLICATE KEY UPDATE instead using the id (because in this moment I'll not have the id)

I can use a where or something like that, cross the product_id and the year would be perfect.

edited Feb 17 '18 at 15:12

Your Common Sense

156,878
40
214
345

asked Jan 31 '18 at 11:49

Augusto Coelho Henriques

1

`on duplicate key` can use any unique key defined on the table. – Gordon Linoff Jan 31 '18 at 11:52
yes i know but this work even if i have a constrain for both (product_id and year)? because i can have the same product_id for different years but not for the same year. – Augusto Coelho Henriques Jan 31 '18 at 12:05
`ON DUPLICATE KEY UPDATE clause` will work only with `UNIQUE index` or `PRIMARY KEY` – Aefits Jan 31 '18 at 12:06
Possible duplicate of [INSERT ... ON DUPLICATE KEY UPDATE with WHERE?](https://stackoverflow.com/questions/2469457/insert-on-duplicate-key-update-with-where) – Daniyal Nasir Jan 31 '18 at 12:08
you are absolutely right @DaniyalNasir – Augusto Coelho Henriques Jan 31 '18 at 12:12
@AugustoCoelhoHenriques Great voteup the comment then :) – Daniyal Nasir Jan 31 '18 at 12:14

Mysql insert on duplicate key update with where alternative

0 Answers0