INSERT if not exists UPDATE if exists does not work

Question

I am trying to have the solution of the very well known INSERT IF NOT EXISTS UPDATE IF EXISTS.

But mine is not working. I don't know why, Can anyone figure it out?

Here is what I have tried yet:

$qprep = ("INSERT INTO gpsdata (`imei`,`latitude`,`longitude`) 
VALUES ('$imei','$lathex1','$lonhex1') ON DUPLICATE KEY UPDATE 
latitude='$lathex1',longitude='$lonhex1';");

I want to update the row if the same "imei" is in there, or Insert if its not. I have my ROW as the primary key and from phpmyadmin, I have made the imei "unique".

What am I doing wrong?

My SQL DUMP:

    CREATE TABLE IF NOT EXISTS `gpsdata` (
  `ROW` int(11) NOT NULL AUTO_INCREMENT,
  `IMEI` varchar(255) NOT NULL,
  `Latitude` varchar(255) NOT NULL,
  `Longitude` varchar(255) NOT NULL,
  PRIMARY KEY (`ROW`),
  UNIQUE KEY `IMEI` (`IMEI`,`Latitude`,`Longitude`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=36 ;

--
-- Dumping data for table `gpsdata`
--

INSERT INTO `gpsdata` (`ROW`, `IMEI`, `Latitude`, `Longitude`) VALUES
(24, '#2:359672050035420:2:*', '90.370803333333', '0'),
(30, '#2:359672050035420:2:*', '90.370803333333', '23.7584'),
(27, '#2:359672050035420:2:*', '90.370803333333', '23.75854'),
(35, '1:135790246811221:1:*', '1.0961283333333', '1.759595'),
(32, '1:135790246811221:1:*', '1.759595', '1.0961283333333');

What error are you getting? you want to update it with new entries or with the same data as before? — 6339, Oct 20 '15 at 12:03
I am not getting any errors. It just adds a new row even if the imei number is same,thats not what i want to do here. I want to update with the new entries (latitude,longitude) can be changed and it will update the row if the imei number exists. If its a new imei it will insert a new row. — hhs, Oct 20 '15 at 12:05
@hhs . . . Then you do not have a unique index/constraint on `imei`, even if you think you do. Or, the new value is subtly different from values in the table (trailing spaces, unseen characters, or something similar). — Gordon Linoff, Oct 20 '15 at 12:06
@hhs You can 'print' the architecture of the table by performing this command: show create table ; (don't forget to include the trailing semicolon!!!) — klaar, Oct 20 '15 at 12:10
@GordonLinoff I have edited the question with sql dump. imei is the unique — hhs, Oct 20 '15 at 12:12
Oh yikes, @hhs, the unique constraint is only forced upon the combination of (`IMEI`,`Latitude`,`Longitude`). That means duplicates of IMEI are allowed to exist if at least they all have a unique combination of values in IMEI, lat and lon. @GordonLinoff was right afterall. — klaar, Oct 20 '15 at 12:14
If you want IMEI to be unique ALWAYS, without depending on values in other columns, then yes. — klaar, Oct 20 '15 at 12:21
I see thats the problem. How do i change that now? I have edited the SQL dump and removed the latitude longitude from there then pasted into the SQL into phpmyadmin. But it's not changing. If i sql dump again its still UNIQUE KEY `IMEI` (`IMEI`,`Latitude`,`Longitude`) — hhs, Oct 20 '15 at 12:30

score 2 · Answer 1 · edited May 23 '17 at 12:23

As seen here, you need to replace the actual values in the update statement with either A| references to the alreay existing values (e.g. longitude=longitude) or B| references to the new values (e.g. longitude=VALUES(longitude), but not longitude='$lonhex1').

Your query should be rewritten:

$qprep = ("INSERT INTO gpsdata (`imei`,`latitude`,`longitude`) 
VALUES ('$imei','$lathex1','$lonhex1') ON DUPLICATE KEY UPDATE 
latitude=VALUES(latitude),longitude=VALUES(longitude)");

Mukesh Joshi · Answer 2 · 2015-10-21T10:07:11.150

You have to pass the column name and its value on which you have used the primary key or unique key on which you want the Duplicate Key Update.

If it gets the id(in your case ROW, latitude and longitude column on which primary and unique key is defined ) in the database, it updates it, else it inserts a new row.

$qprep = ("INSERT INTO gpsdata (`imei`,`latitude`,`longitude`) 
VALUES ('$imei','$lathex1','$lonhex1') ON DUPLICATE KEY UPDATE 
latitude=VALUES(latitude),longitude=VALUES(longitude)");

Example:

INSERT INTO gpsdata (`row`,`imei`,`latitude`,`longitude`) 
VALUES ('24','1','TEST','TEST') ON DUPLICATE KEY UPDATE 
`IMEI`='2', `Latitude`='2',`Longitude`='2';

or

INSERT INTO gpsdata (`imei`,`latitude`,`longitude`) 
VALUES ('1','TEST','TEST') ON DUPLICATE KEY UPDATE 
`IMEI`='2', `Latitude`=VALUES(`Latitude`),`Longitude`=VALUES(`Longitude);

A little addendum: it isn't restricted to detection of duplicates on a primary key column, but on any column to which the unique constraint is added. Since primary key is a special case of unique key, the statement holds. — klaar, Oct 21 '15 at 06:25

score 0 · Answer 3 · answered Oct 20 '15 at 13:20

If you have statement based replication running on this server then there would be a problem, see the warning below:

Unsafe statement written TO the BINARY LOG USING statement FORMAT 
since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  
ON a TABLE WITH more THAN ONE UNIQUE KEY IS unsafe

INSERT if not exists UPDATE if exists does not work

3 Answers3