在线观看不卡亚洲电影_亚洲妓女99综合网_91青青青亚洲娱乐在线观看_日韩无码高清综合久久

鍍金池/ 問答/數(shù)據(jù)庫/ mysql insert select on duplicate key顯示某個

mysql insert select on duplicate key顯示某個字段is ambiguous

圖片描述

這個表的name和type字段是聯(lián)合唯一索引，我想實現(xiàn)把type=1的數(shù)據(jù)重新insert到這個表變?yōu)?code>type=0，當沖突時更新aa = CONCAT(aa,VALUES(aa))，但是報錯Column 'aa' in field list is ambiguous，我加了b.aa也還是這樣，求大佬指點, 謝謝。
sql：

INSERT INTO b(`name`,`aa`,`type`)
SELECT `name`, aa, 0
FROM b 
WHERE `type`=1
ON DUPLICATE KEY UPDATE `aa` = CONCAT(`aa`,VALUES(`aa`))
// 表結(jié)構(gòu)和數(shù)據(jù)如下
CREATE TABLE `b` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(66) COLLATE utf8_general_mysql500_ci NOT NULL DEFAULT '' COMMENT 'english remark',
  `aa` varchar(33) COLLATE utf8_general_mysql500_ci NOT NULL,
  `type` tinyint(1) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `NewIndex1` (`name`,`type`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

insert  into `b`(`id`,`name`,`aa`,`type`) values (4,'a','{2017,01,01}',0),(5,'a','{2017,02,01}',1),(7,'b','{2017,01,11}',0),(8,'b','{2017,01,01}',1),(16,'c','{2017,01,01}3',1);
回答
編輯回答
撿肥皂
INSERT INTO b(`name`,`aa`,`type`)
SELECT `name`, `aa`, 0
FROM b AS c
WHERE `type`=1
ON DUPLICATE KEY UPDATE b.aa = CONCAT(b.aa,c.aa)

這個意思？

+----+------+--------------------------+------+
| id | name | aa                       | type |
+----+------+--------------------------+------+
| 4  | a    | {2017,01,01}{2017,02,01} | 0    |
| 5  | a    | {2017,02,01}             | 1    |
| 7  | b    | {2017,01,11}{2017,01,01} | 0    |
| 8  | b    | {2017,01,01}             | 1    |
| 16 | c    | {2017,01,01}3            | 1    |
| 17 | c    | {2017,01,01}3            | 0    |
+----+------+--------------------------+------+
2017年9月28日 15:42