CODEWITHSUNDEEP
mysql
Jacob Brunson
Jacob Brunson

Reputation: 1492

MySQL - INSERT ON DUPLICATE KEY - 2 columns

I am going to be logging the IP addresses that my users log in with. To do this, I have a simple table with 3 columns: user, ip, time. time is the time at which they last logged in with that address.

When they log in, I want to insert a row into the database containing the IP they logged in with, and the time they logged in.

If a row with the same user AND same ip already exists, I just want to update the time. A more general question:

How can I INSERT a row, or UPDATE it if two (or more) of its columns are the same?

Upvotes: 7

Views: 2969

Answers (1)

zerkms
zerkms

Reputation: 255105

You just need to create composite unique key user + ip and use INSERT ON DUPLICATE KEY UPDATE

INSERT INTO tbl (user, ip, `time`) VALUES (1, '1.2.3.4', NOW())
ON DUPLICATE KEY UPDATE `time` = NOW()

To create index use something like

CREATE UNIQUE INDEX tbl_user_ip ON tbl (user, ip)

Upvotes: 16

Related Questions