Insert or update (if exists) without primary key

Question

I have the following database MySQL table.

• id (PK, AI)
• email
• country
• lastlogin

I have a regular query in PHP that inserts this into the table. however, logically, if this code runs several times, the same row will be inserted to the database every time.

I want my reference for checking and duplication to be the email field, and if the email is the same, update the country and the lastlogin.

I checked on other questions for a similar issue and the suggested way was to use ON DUPLICATE KEY like this

INSERT INTO <table> (field1, field2, field3, ...) 
VALUES ('value1', 'value2','value3', ...)
ON DUPLICATE KEY UPDATE
field1='value1', field2='value2', field3='value3', ...

However, my primary key is not the email field rather the id but I don't want to run the check on it.

Answer 1

One option is make the email field unique, and then it should behave the same as primary key, at least with regard to MySQL's ON DUPLICATE KEY UPDATE:

ALTER TABLE yourTable ADD UNIQUE INDEX `idx_email` (`email`);

and then:

INSERT INTO yourTable (email, country, lastlogin)
VALUES ('tony9099@stackoverflow.com', 'value2', 'value3')
ON DUPLICATE KEY UPDATE
    email='value1', country='value2', lastlogin='value3'

If the email tony9099@stackoverflow.com already exists in your table, then the update would kick in with alternative values.

From the MySQL documentation:

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row.

This approach doesn't only work with primary keys, it also works with any column having a unique index.

Answer 2

As Dan has mentioned, the ROW_COUNT() in-built function does not support this solution with a standard configuration.

MySQL::ROW_COUNT()

For UPDATE statements, the affected-rows value by default is the number of rows actually changed. If you specify the CLIENT_FOUND_ROWS flag to mysql_real_connect() when connecting to mysqld, the affected-rows value is the number of rows “found”; that is, matched by the WHERE clause.

If modifying the database schema is not an option, you could use the following method:

UPDATE `table` SET `country`='value1', `lastlogin`='value1' WHERE `email`='value3'

IF ROW_COUNT()=0
    INSERT INTO `table` (`email`, `country`, `lastlogin`) VALUES ('value1', 'value2', 'value3')

Answer 3

you can use

$query=mysql_query("select * from table where email = 'your email'");
if(mysql_num_rows($query) > 0){
//update
}else{
//insert
}

Answer 4

You can load a row with the given email first and then decide if you have to insert or update depending on the existence of the loaded row. This needs multiple SQL statements, but it can be written in a DBMS vendor independent way. Use a surrounding transaction to handle concurrency. An index on the email-column is useful to keep the existence - check fast. Adding a unique - constraint on the email-column is an option to guarantee that there will never be multiple rows with same email.

Answer 5

You can do it manually like before inserting the value to table first check whether the value exists in table or not if yes then update your related field

    $qry = mysql_query("select * from table where email='abc@abc.com'");
    $count = mysql_num_rows($qry);
    if($count > 0){
       write your update query here
   }else{
       write your insert query here
  }