CODEWITHSUNDEEP
phpmysqlpdosql-updatesql-insert
Neel
Neel

Reputation: 9880

How to Update on Duplicate Key if the columns are not unique?

How do I insert only when the 2 row values are not the same exactly? Here is my scenario so my question makes sense:

ID      employee_id     client_id
1           1              10
2           2              11
3           1              11
4           2              10

I want to insert something like this to the column in PDO:

insert into yourTableName (employee_id, client_id)
    values (:employee_id, :client_id)
    on duplicate key update 
        employee_id=:$employee_id2, client_id=:client_id2

But the insert on duplicate above only seems to work if one of them columns were unique. In my case, the values in employee_id and client_id are not unique and the values can repeat multiple times. However there can only be 1 combination of it.

How can I only insert the row only if the exact combination of column values doesnt exit?

Upvotes: 2

Views: 185

Answers (3)

Geoffroy
Geoffroy

Reputation: 11

How can I only insert the row only if the exact combination of column values doesnt exit?

To do that you have to create a unique key with both column "employee_id" and "client_id".

Upvotes: 1

Marcus Adams
Marcus Adams

Reputation: 53860

INSERT ... ON DUPLICATE KEY UPDATE ... requires a unique key. The unique key can be on multiple columns. Simply create a multi-column unique key on both columns (employee_id, client_id).

There is a caveat to using INSERT ... ON DUPLICATE KEY UPDATE .... With default settings, if ID is an auto-increment column, the counter will increment each time you call INSERT ... ON DUPLICATE KEY UPDATE ... even if it just updates because the next id is generated first. If that is a problem, you should use SELECT FOR UPDATE within a transaction to see if the record exists, then behave accordingly.

Upvotes: 3

lafor
lafor

Reputation: 12776

UNIQUE constraint doesn't have to be limited to a single column. You can alter your table like this:

ALTER TABLE `yourTableName` ADD UNIQUE `unique_index`(`employee_id`, `client_id`);

Upvotes: 1

Related Questions