| Bug #19343 | Error with UPDATE | ||
|---|---|---|---|
| Submitted: | 25 Apr 2006 17:10 | Modified: | 26 Apr 2006 12:18 |
| Reporter: | Jacek Kowalski | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.19 | OS: | Linux (SuSE 9.1) |
| Assigned to: | CPU Architecture: | Any | |
[26 Apr 2006 1:06]
Peter Laursen
A variation on MySQL 5.1.9
CREATE TABLE test (
id varchar(2) NOT NULL,
update1 tinyint(4) NOT NULL,
update2 tinyint(4) NOT NULL,
PRIMARY KEY (id)
);
REPLACE INTO `test` (`id`) VALUES ('aa');
REPLACE INTO `test` (`id`) VALUES ('bb');
UPDATE `test` SET `update1`=`update1`+1, `update2`=`update2`+2 WHERE `id`='aa';
UPDATE `test` SET `update1`=`update1`+1, `update2`=`update2`+2 WHERE `id`='bb';
REPLACE INTO `test` (`id`) VALUES ('aa');
REPLACE INTO `test` (`id`) VALUES ('bb');
SELECT * FROM `test`;
/*
RETURNS
id update1 update2
------ ------- -------
aa 0 0
bb 0 0
*/
... so obviously 'REPLACE INTO' 'zero's' non-referenced columns.
[26 Apr 2006 1:24]
Peter Laursen
I forgot to mention that I did this on WinXP.
[26 Apr 2006 12:18]
Valeriy Kravchuk
The behaviour you described is correct (for REPLACE statement). Please, reread the manual (http://dev.mysql.com/doc/refman/5.0/en/replace.html): "REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted."
Description: I'd like to increment the value using REPLACE (to create row, if user do not exists) and UPDATE (increment existing row). Confirmed: there aren't any problems on MySQL 4.1.15. How to repeat: CREATE TABLE test ( id varchar(2) NOT NULL, update1 tinyint(4) NOT NULL, update2 tinyint(4) NOT NULL, PRIMARY KEY (id) ); REPLACE INTO `test` (`id`) VALUES ('aa'); REPLACE INTO `test` (`id`) VALUES ('bb'); UPDATE `test` SET `update1`=`update1`+1, `update2`=`update2`+2 WHERE `id`='aa'; UPDATE `test` SET `update1`=`update1`+1, `update2`=`update2`+2 WHERE `id`='bb'; # update1 = 1, update2 = 2 REPLACE INTO `test` (`id`) VALUES ('aa'); REPLACE INTO `test` (`id`) VALUES ('bb'); UPDATE `test` SET `update1`=`update1`+1, `update2`=`update2`+2 WHERE `id`='aa'; UPDATE `test` SET `update1`=`update1`+1, `update2`=`update2`+2 WHERE `id`='bb'; # update1 = 1, update2 = 2 NOT update1 = 2, update2 = 4 (as in MySQL 4.1.15) Suggested fix: First get informations using SELECT. Increment variable. Save using UPDATE.