I want to update two tables with one UPDATE query. I use a simple LEFT JOIN for that. Curiously not all matching rows are changed. This are the example tables:
DROP TABLE IF EXISTS `test_continents`, `test_agents`;
CREATE TABLE IF NOT EXISTS `test_continents` (
`key` char(2) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED;
INSERT INTO `test_continents` (`key`) VALUES('EU');
INSERT INTO `test_continents` (`key`) VALUES('NA');
CREATE TABLE IF NOT EXISTS `test_agents` (
`name` char(64) COLLATE utf8_unicode_ci NOT NULL,
`continent` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`name`),
KEY `continent` (`continent`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED;
INSERT INTO `test_agents` (`name`, `continent`) VALUES('006', 'EU');
INSERT INTO `test_agents` (`name`, `continent`) VALUES('007', 'EU');
INSERT INTO `test_agents` (`name`, `continent`) VALUES('008', 'NA');
INSERT INTO `test_agents` (`name`, `continent`) VALUES('009', 'NA');
OPTIMIZE TABLE `test_continents`, `test_agents`;
This is the sample SELECT query, it does match all rows:
SELECT
`test_agents`.`continent`,
`test_agents`.`name`
FROM `test_continents`
LEFT JOIN `test_agents` ON (
`test_agents`.`continent` = `test_continents`.`key`
)
WHERE
`test_continents`.`key` = 'NA'
This is the UPDATE query:
UPDATE `test_continents`
LEFT JOIN `test_agents` ON (
`test_agents`.`continent` = `test_continents`.`key`
)
SET
`test_continents`.`key` = 'SA',
`test_agents`.`continent` = `test_continents`.`key`
WHERE
`test_continents`.`key` = 'NA'
After the update, the tables look like this:
test_continents:
key
EU
SA
test_agents:
name continent
006 EU
007 EU
008 SA
009 NA
Can anyone explain me why the continent field for test_agent 009 is still 'NA' after the UPDATE?
It seems that this does not happen if I use INT fields, but I need CHAR(2) fields, so please don't discuss about the table structure, just say me is this a bug, a feature or anything else?
I am using Ubuntu 12.04 and MySQL 5.5.29-1~dotdeb.0 I am coder since more than 15 years and normaly I don't ask such lamer questions but I can not explain it and I need help...
UPDATE:
I have made a second example with INT(2) fields instead of CHAR(2). The UPDATE match all rows in this case:
CREATE TABLE IF NOT EXISTS `test_agents` (
`name` char(64) COLLATE utf8_unicode_ci NOT NULL,
`continent` int(2) NOT NULL DEFAULT '0',
PRIMARY KEY (`name`),
KEY `continent` (`continent`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED;
INSERT INTO `test_agents` (`name`, `continent`) VALUES('006', 1);
INSERT INTO `test_agents` (`name`, `continent`) VALUES('007', 1);
INSERT INTO `test_agents` (`name`, `continent`) VALUES('008', 2);
INSERT INTO `test_agents` (`name`, `continent`) VALUES('009', 2);
CREATE TABLE IF NOT EXISTS `test_continents` (
`key` int(2) NOT NULL,
PRIMARY KEY (`key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED;
INSERT INTO `test_continents` (`key`) VALUES(1);
INSERT INTO `test_continents` (`key`) VALUES(2);
--
SELECT
`test_agents`.`continent`,
`test_agents`.`name`
FROM `test_continents`
LEFT JOIN `test_agents` ON (
`test_agents`.`continent` = `test_continents`.`key`
)
WHERE
`test_continents`.`key` = 2
--
UPDATE `test_continents`
LEFT JOIN `test_agents` ON (
`test_agents`.`continent` = `test_continents`.`key`
)
SET
`test_continents`.`key` = 3,
`test_agents`.`continent` = `test_continents`.`key`
WHERE
`test_continents`.`key` = 2
test_agents after the UPDATE:
name continent
006 1
007 1
008 3
009 3
--
I can not believe that this is a normal behavior. The only difference between both examples is the field type, so it should affect the same rows or not? I am sure the queries are 100% correct.
Has anyone an explanation???
Thanks in advance.
