CODEWITHSUNDEEP
mysql
Vera Perrone
Vera Perrone

Reputation: 369

Insert into a MySQL table or update if exists without a unique key

I know there is one way to accomplish this result using a unique key, for example ID, but as you can see below in my case this won't work because I don't have one unique key.

INSERT INTO User_Status (User_ID1, User_ID2, Status) VALUES(?, ?, ?) ON DUPLICATE KEY UPDATE Status = ?

The thing is I don't have one unique ID on this tabel, however there are 3 current options. 1) Twoo users don't have one status together, in other words: INSERT it in the tabel. 2) User 1 has one status with user 2 (The 1-2 combination however IS UNIQUE). 3) Same as case 2, but turned around: user 2 has one status with user 1 ( Again this combination IS UNIQUE).

Is there anyway to Update if it already Exist with this options?

Sample Data:

User_ID1     User_ID2     Status
1            4            has_send_request
4            1            has_received_request
4            2            friends
2            4            friends

Let's say I want to update the status of user 1-4 from has_send_request to friends. In this case I can do a normal UPDATE query. However If I want to UPDATE the status from the users 2-4 I need to make one INSERT query and not one UPDATE query.

Upvotes: 0

Views: 170

Answers (1)

Learning
Learning

Reputation: 163

Please see attached image on how to create a unique key.

enter image description here

Upvotes: 1

Related Questions