Mysql insert into duplicate key on a non-duplicate key

Question

I use PHP and Mysql.

This SQL works:

INSERT INTO products (id, title, description)
            VALUES (10, 'value1', 'value2')
            ON DUPLICATE KEY UPDATE
            id=10,
            title='value25',
            description='value2'

My id is a primary key and therefor it works. The other fields are varchars.

My real case is a bit different. Look at this:

Type is introduced and together with sku it's unique.

id    sku    title    description    type
1     abc                            one
2     abc                            two
3     def                            one

So my "real" key is the sku that I want to use and it's not unique by its own. It can not be in my case. But together with type it is unique.

Look below and it might be more clear:

abc-one // Unique combination
abc-two // Unique combination
def-one // Unique combination

Is it possible to use a multi insert/update SQL query in this case?

For my comprehension: How does your update query looks like without this example values? And what do you exactly mean with "multi query"? — rbr94
– rbr94, Commented Sep 22, 2016 at 13:35
@rbr94 My current update query is the sql on top of the question. It's an insert but also an update ` ON DUPLICATE KEY UPDATE`. — Jens Törnell
– Jens Törnell, Commented Sep 22, 2016 at 13:38
so why don't you post the real problem instead of a hypothetical one?? — e4c5
– e4c5, Commented Sep 22, 2016 at 13:39
How do you want to react to a duplicate key event for your two unique columns? — rbr94
– rbr94, Commented Sep 22, 2016 at 13:40
@e4c5 This is my real problem. The only difference is that my sku looks like this: dc848d57-f459-4514-93bb-5649b22c648c` but it's harder to read so I made them simpler in this question. — Jens Törnell
– Jens Törnell, Commented Sep 22, 2016 at 13:42

rbr94 · Accepted Answer · 2016-09-22 13:52:32Z

1

If you define the columns sku and type as unique columns, the ON DUPLICATE KEY UPDATE expression will also work as well as e.g. with only one PrimaryKey in your products table.

Example (based on your data):

id    sku    title    description    type
1     abc                            one
2     abc                            two
3     def                            one

//INSERT
INSERT INTO products (id, sku, type) VALUES (4, 'ghi', 'one') ON DUPLICATE KEY UPDATE ... 

//UPDATE dataset with ID=1
INSERT INTO products (id, sku, type) VALUES (1, 'ghi', 'two') ON DUPLICATE KEY UPDATE ... 

//UPDATE dataset with sku='abc' and type='one'
INSERT INTO products (id, sku, type) VALUES (5, 'abc', 'one') ON DUPLICATE KEY UPDATE ...

Therefore see the MySQL documentation: https://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html

answered Sep 22, 2016 at 13:52

rbr94

2,2874 gold badges27 silver badges40 bronze badges

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

1 Comment

Jens Törnell Over a year ago

I did not want to complicate it more than needed so I've already merged them and set it as a new id without auto increasment. I don't think I will use the id later so it should be good for performance anyway. Thanks!

Collectives™ on Stack Overflow

Mysql insert into duplicate key on a non-duplicate key

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