Menu
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?
796
Find Duplicate Row values in Multiple ColumnsSELECT col1, col2,..., COUNT(*) FROM table_name GROUP BY col1, col2, ... HAVING (COUNT(col1) > 1) AND (COUNT(col2) > 1) AND ... In the above query, we do a GROUP BY of all the columns (col1, col2) for whom we want to find duplicates.
By definition, atomicity requires that each transaction is an all or nothing. So yes it is atomic in the sense that if the data that you are trying to insert will cause a duplicate in the primary key or in the unique index, the statement will instead perform an update and not error out.
The Insert on Duplicate Key Update statement is the extension of the INSERT statement in MySQL. When we specify the ON DUPLICATE KEY UPDATE clause in a SQL statement and a row would cause duplicate error value in a UNIQUE or PRIMARY KEY index column, then updation of the existing row occurs.
There are two basic syntax of INSERT INTO statement is as follows: INSERT INTO TABLE_NAME (column1, column2, column3,... columnN)] VALUES (value1, value2, value3,... valueN);
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
