CODEWITHSUNDEEP
mysql
Mark Henry
Mark Henry

Reputation: 2699

insert record on duplicate update is not updating

I am using this insert query which is supposed to update on duplicate situation. It does not update and does not result in error. What is wrong here?

Primary key are the columns res_id, lud

INSERT INTO sv_sa (res_id,resort,resort_us,weather,templo,temphi,alert_val,alert,ski_id,lud,tweet)
     VALUES (1561,'Aachen','aachen','PM Rain/Snow',-1,3,2,4,NULL,'2014-01-25',0)
         ON DUPLICATE KEY UPDATE templo=-1, temphi=3, alert_val=2, alert=4, ski_id=NULL

CREATE TABLE `sv_sa` (
  `res_id` int(6) NOT NULL,
  `resort` varchar(30) DEFAULT NULL,
  `resort_us` varchar(30) DEFAULT NULL,
  `ski_id` int(4) DEFAULT NULL,
  `templo` decimal(4,2) DEFAULT NULL,
  `temphi` decimal(4,2) DEFAULT NULL,
  `weather` varchar(50) DEFAULT NULL,
  `alert_val` int(3) DEFAULT NULL,
  `alert` int(3) DEFAULT NULL,
  `snow_valley_min` int(4) DEFAULT NULL,
  `snow_valley_max` int(4) DEFAULT NULL,
  `snow_mountain` int(4) DEFAULT NULL,
  `lifts_open` varchar(8) DEFAULT NULL,
  `tweet` tinyint(4) DEFAULT NULL,
  `lud` date NOT NULL,
  PRIMARY KEY (`res_id`,`lud`),
  KEY `alert` (`alert`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Upvotes: 0

Views: 42

Answers (1)

Rabtok
Rabtok

Reputation: 9

On duplicate values are the same as the record inserted so there is no change to the record, change one of the duplicate values, say

temphi=5

try it and it will set 

temphi=5

on subsequent inserts.

Upvotes: 1

Related Questions