Description:
I'm trying to merge content from table "clickstream2" to table "clickstream",
my statement is:
insert into clickstream(user, action, day)
select a2.user, a2.action, a2.day
from
(select c1.user, c1.action, c1.day, md5(concat(c1.user, c1.action, c1.day)) as c1md5 from clickstream c1) a1
right outer join
(select c2.user, c2.action, c2.day, md5(concat(c2.user, c2.action, c2.day)) as c2md5 from clickstream2 c2) a2
on a1.c1md5 = a2.c2md5
where
isnull(a1.c1md5)
on duplicate key update user = a2.user, action = a2.action, day = a2.day
and it ends with error:
>Unknown table 'a2' in field list
it seems that the "on duplicate key update" clause fails to recon table aliases.
table definition follows:
--
-- Table structure for table `clickstream`
--
DROP TABLE IF EXISTS `clickstream`;
CREATE TABLE `clickstream` (
`user` varchar(45) default NULL,
`action` varchar(45) default NULL,
`day` datetime default NULL,
KEY `Index_1` (`action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `clickstream`
--
/*!40000 ALTER TABLE `clickstream` DISABLE KEYS */;
LOCK TABLES `clickstream` WRITE;
INSERT INTO `clickstream` VALUES ('a','login','2005-04-05 00:00:00'),('a','click','2005-05-05 00:00:00'),('b','logout','2005-03-02 00:00:00'),('b','click','2005-01-25 00:00:00');
UNLOCK TABLES;
/*!40000 ALTER TABLE `clickstream` ENABLE KEYS */;
--
-- Table structure for table `clickstream2`
--
DROP TABLE IF EXISTS `clickstream2`;
CREATE TABLE `clickstream2` (
`user` varchar(45) default NULL,
`action` varchar(45) default NULL,
`day` datetime default NULL,
KEY `Index_1` (`action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `clickstream2`
--
/*!40000 ALTER TABLE `clickstream2` DISABLE KEYS */;
LOCK TABLES `clickstream2` WRITE;
INSERT INTO `clickstream2` VALUES ('a','login','2005-04-05 00:00:00'),('a','click','2005-05-05 00:00:00'),('b','clock','2005-03-02 00:00:00'),('b','clock','2005-01-25 00:00:00');
UNLOCK TABLES;
How to repeat:
Create and populate tables as above, then issue:
select a2.user, a2.action, a2.day
from
(select c1.user, c1.action, c1.day, md5(concat(c1.user, c1.action, c1.day)) as c1md5 from clickstream c1) a1
right outer join
(select c2.user, c2.action, c2.day, md5(concat(c2.user, c2.action, c2.day)) as c2md5 from clickstream2 c2) a2
on a1.c1md5 = a2.c2md5
where
isnull(a1.c1md5)
output is of two rows (that's fine, those are the changed rows i.e. the differencies between clickstream and clickstream2), now issue:
insert into clickstream(user, action, day)
select a2.user, a2.action, a2.day
from
(select c1.user, c1.action, c1.day, md5(concat(c1.user, c1.action, c1.day)) as c1md5 from clickstream c1) a1
right outer join
(select c2.user, c2.action, c2.day, md5(concat(c2.user, c2.action, c2.day)) as c2md5 from clickstream2 c2) a2
on a1.c1md5 = a2.c2md5
where
isnull(a1.c1md5)
even this one runs fine, the tables are without keys, so data is just added to the table, now run:
insert into clickstream(user, action, day)
select a2.user, a2.action, a2.day
from
(select c1.user, c1.action, c1.day, md5(concat(c1.user, c1.action, c1.day)) as c1md5 from clickstream c1) a1
right outer join
(select c2.user, c2.action, c2.day, md5(concat(c2.user, c2.action, c2.day)) as c2md5 from clickstream2 c2) a2
on a1.c1md5 = a2.c2md5
where
isnull(a1.c1md5)
on duplicate key update user = a2.user, action = a2.action, day = a2.day
and you get the error reported above.
In case you think that the problem lies in lack of keys (would be logical) I got the error with a table with keys, where the insert ... select (without "on duplicate key update" clause correctly fails:
DROP TABLE IF EXISTS `test`.`clickstream`;
CREATE TABLE `clickstream` (
`user` varchar(45) NOT NULL,
`action` varchar(45) NOT NULL,
`day` datetime NOT NULL,
PRIMARY KEY (`user`, `day`),
KEY `Index_1` (`action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Description: I'm trying to merge content from table "clickstream2" to table "clickstream", my statement is: insert into clickstream(user, action, day) select a2.user, a2.action, a2.day from (select c1.user, c1.action, c1.day, md5(concat(c1.user, c1.action, c1.day)) as c1md5 from clickstream c1) a1 right outer join (select c2.user, c2.action, c2.day, md5(concat(c2.user, c2.action, c2.day)) as c2md5 from clickstream2 c2) a2 on a1.c1md5 = a2.c2md5 where isnull(a1.c1md5) on duplicate key update user = a2.user, action = a2.action, day = a2.day and it ends with error: >Unknown table 'a2' in field list it seems that the "on duplicate key update" clause fails to recon table aliases. table definition follows: -- -- Table structure for table `clickstream` -- DROP TABLE IF EXISTS `clickstream`; CREATE TABLE `clickstream` ( `user` varchar(45) default NULL, `action` varchar(45) default NULL, `day` datetime default NULL, KEY `Index_1` (`action`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `clickstream` -- /*!40000 ALTER TABLE `clickstream` DISABLE KEYS */; LOCK TABLES `clickstream` WRITE; INSERT INTO `clickstream` VALUES ('a','login','2005-04-05 00:00:00'),('a','click','2005-05-05 00:00:00'),('b','logout','2005-03-02 00:00:00'),('b','click','2005-01-25 00:00:00'); UNLOCK TABLES; /*!40000 ALTER TABLE `clickstream` ENABLE KEYS */; -- -- Table structure for table `clickstream2` -- DROP TABLE IF EXISTS `clickstream2`; CREATE TABLE `clickstream2` ( `user` varchar(45) default NULL, `action` varchar(45) default NULL, `day` datetime default NULL, KEY `Index_1` (`action`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `clickstream2` -- /*!40000 ALTER TABLE `clickstream2` DISABLE KEYS */; LOCK TABLES `clickstream2` WRITE; INSERT INTO `clickstream2` VALUES ('a','login','2005-04-05 00:00:00'),('a','click','2005-05-05 00:00:00'),('b','clock','2005-03-02 00:00:00'),('b','clock','2005-01-25 00:00:00'); UNLOCK TABLES; How to repeat: Create and populate tables as above, then issue: select a2.user, a2.action, a2.day from (select c1.user, c1.action, c1.day, md5(concat(c1.user, c1.action, c1.day)) as c1md5 from clickstream c1) a1 right outer join (select c2.user, c2.action, c2.day, md5(concat(c2.user, c2.action, c2.day)) as c2md5 from clickstream2 c2) a2 on a1.c1md5 = a2.c2md5 where isnull(a1.c1md5) output is of two rows (that's fine, those are the changed rows i.e. the differencies between clickstream and clickstream2), now issue: insert into clickstream(user, action, day) select a2.user, a2.action, a2.day from (select c1.user, c1.action, c1.day, md5(concat(c1.user, c1.action, c1.day)) as c1md5 from clickstream c1) a1 right outer join (select c2.user, c2.action, c2.day, md5(concat(c2.user, c2.action, c2.day)) as c2md5 from clickstream2 c2) a2 on a1.c1md5 = a2.c2md5 where isnull(a1.c1md5) even this one runs fine, the tables are without keys, so data is just added to the table, now run: insert into clickstream(user, action, day) select a2.user, a2.action, a2.day from (select c1.user, c1.action, c1.day, md5(concat(c1.user, c1.action, c1.day)) as c1md5 from clickstream c1) a1 right outer join (select c2.user, c2.action, c2.day, md5(concat(c2.user, c2.action, c2.day)) as c2md5 from clickstream2 c2) a2 on a1.c1md5 = a2.c2md5 where isnull(a1.c1md5) on duplicate key update user = a2.user, action = a2.action, day = a2.day and you get the error reported above. In case you think that the problem lies in lack of keys (would be logical) I got the error with a table with keys, where the insert ... select (without "on duplicate key update" clause correctly fails: DROP TABLE IF EXISTS `test`.`clickstream`; CREATE TABLE `clickstream` ( `user` varchar(45) NOT NULL, `action` varchar(45) NOT NULL, `day` datetime NOT NULL, PRIMARY KEY (`user`, `day`), KEY `Index_1` (`action`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;