mySQL how to update and replace

Question

I need to insert records, but if the records exist do a replace instead. Here is what I am currently using:

  $sessionDate = date("Y-m-d H:i:s");
  foreach($tmpVP as $sessionVP) {
    $res = mysql_query("INSERT INTO sessions
                      (sessionID,sessionDate,sessionVS,sessionVP)
                      VALUES('$sessionID','$sessionDate','$sessionVS',
                      '$sessionVP')") ;
  }

What I really need is to update any records matching sessionID, sessionVS, and sessionVP and insert new records that don't match.

EDIT:

Table definition

CREATE TABLE `sessions` (
 `ID` bigint(20) NOT NULL auto_increment,
 `sessionID` varchar(36) NOT NULL,
 `sessionDate` datetime NOT NULL,
 `sessionUser` bigint(20) NOT NULL,
 `sessionVS` varchar(255) NOT NULL,
 `sessionVP` bigint(20) NOT NULL,
 `reserved` int(11) NOT NULL,
 PRIMARY KEY  (`ID`),
 KEY `ID` (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=88 DEFAULT CHARSET=utf8

sessionID, sessionVP, and sessionVS are not unique. Sample:

sessionID                  sessionDate          sessionUser sessionVS sessionVP 
0t1m58q9ktejuhqlrjqglcoia0 2010-06-20 09:20:53  0           111111    144268736
0t1m58q9ktejuhqlrjqglcoia0 2010-06-20 09:20:53  0           111111    144268819
0t1m58q9ktejuhqlrjqglcoia0 2010-06-20 09:20:53  0           111111    144268880

So, if I have a new record with 0t1m58q9ktejuhqlrjqglcoia0, 111111, and `144268880' I need to update row #3 instead of adding row #4.

Answer 1

Based on your table structure, I mean the primary keys you can use

$res = mysql_query("REPLACE INTO sessions
                      (sessionID,sessionDate,sessionVS,sessionVP)
                      VALUES('$sessionID','$sessionDate','$sessionVS',
                      '$sessionVP')") ;

Answer 2

Add an unique key on (sessionID, sessionVS, sessionVP), then use REPLACE instead of INSERT (just substitute the word, syntax is the same).

Answer 3

Firstly you should add a unique index on (sessionID, sessionVP, sessionVS). You can do this using the following command:

CREATE UNIQUE INDEX ux_sessions_sessionid_sessionvs_sessionvp
ON sessions (sessionID, sessionVS, sessionVP)

Then there are two relatively simple ways to insert or update in MySQL. The first is to use ON DUPLICATE UPDATE:

INSERT INTO sessions
(sessionID,sessionDate,sessionVS,sessionVP)
VALUES
('$sessionID', '$sessionDate', '$sessionVS', '$sessionVP')
ON DUPLICATE KEY UPDATE sessionDate = '$sessionDate'

There other is to use REPLACE:

REPLACE INTO sessions
(sessionID,sessionDate,sessionVS,sessionVP)
VALUES
('$sessionID', '$sessionDate', '$sessionVS', '$sessionVP')

The second is slightly more concise, but has the disadvantage that it internally causes a delete followed by an insert.

There are also a few other issues:

• You don't need both a primary key index and an ordinary index on ID. Remove the ordinary index as it is redundant.

• You may have an SQL vulnerability. If you have not already validated the input you might want to consider protecting yourself by using mysql_real_escape_string or intval as appropriate. Alternatively you could look at using query parameters.

• You are not checking for error conditions. Consider using trigger_error so that if your query has an error you can see what the error is. Seeing the error message can save you a lot of time debugging.

  mysql_query("...") or trigger_error(mysql_error());
  

Answer 4

You might take a look at INSERT ... ON DUPLICATE KEY UPDATE: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Answer 5

Are the values part of the primary key? If yes, take a look at: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html