Efficient way to check if composite key exists before inserting

Question

I am using mysql Nodejs package but I will tag this question as mysql only as people with mysql knowledge should be able to help me out.

This question is more about finding an efficient way to write a query.

I have a table with a composite key of partID,orderID and stageID.

I need to insert into this table if there are no previous composite key else update the other fields if a row with these composite keys were found.

I was going to do a select query where condition of these 3 columns and then in case of a result do an update else do an insert.

Is this the best way to handle this situation. Sorry I do not have much experience with SQL databases.

Possible duplicate of [How do I update if exists, insert if not (AKA "upsert" or "merge") in MySQL?](https://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql) — sticky bit, Jul 21 '19 at 20:42

score 0 · Answer 1 · answered Jul 21 '19 at 20:46

Simply define the composite key as unique or create a unique index on them:

alter table t add constraint unq_t_3 unique (partID, orderID stageID);

If you attempt to insert a row with duplicate keys, then you will get an error. If you want to avoid the error, use on duplicate key update:

insert into t (partID, orderID, stageID, . . .)
    values (?, ?, ?, . . .)
    on duplicate key update partID = values(partID);

The set in the on duplicate key does not do anything except prevent a duplicate error.

1 Answers1