CODEWITHSUNDEEP
mysql
transilvlad
transilvlad

Reputation: 14532

MySQL update not updating

The select works and returns 1 result.

SELECT * FROM `list` WHERE `email` = '[email protected]' AND `cid` = 1;

This update works, no errors, no records affected!!!

UPDATE `list` 
  SET `message` = 'test', `status` = 0 
WHERE `email` = '[email protected]' AND `cid` = 1;

Table structure

CREATE TABLE `list` (
    `id` BIGINT(19) NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(255) NULL DEFAULT NULL,
    `cid` INT(10) NOT NULL DEFAULT '0',
    `message` TEXT NULL,
    `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `status` TINYINT(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    INDEX `cid` (`cid`),
    INDEX `date` (`date`),
    INDEX `status` (`status`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

Upvotes: 0

Views: 3945

Answers (1)

Jocelyn
Jocelyn

Reputation: 11413

The MySQL server reports no records were affected because, even though one row matches the criteria, it doesn't need to be updated: it already has message = 'test' and status = 0.

Try this query if you want to always update the date field:

UPDATE `list` 
  SET `message` = 'test', `status` = 0, `date`=NOW()
WHERE `email` = '[email protected]' AND `cid` = 1;

Upvotes: 3

Related Questions