表结构如下:DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`uid` varchar(255) NOT NULL,
`uname` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;INSERT INTO `test` VALUES ('1', '1', 'a');
INSERT INTO `test` VALUES ('2', '2', 'a');
INSERT INTO `test` VALUES ('3', '1', 'b');
INSERT INTO `test` VALUES ('4', '2', 'b');
INSERT INTO `test` VALUES ('5', '3', 'b');我想实现的操作是:
把uname=b,并且,uid不在a中的记录,uname更改为a
即更新的结果为:
id uid uname
1 1 a
2 2 a
3 1 b
4 2 b
5 3 a我是这样写的,但出错:update test set uname = N'a' where uname = N'b' and uid not in (select uid from test where uname = N'a')出错信息:
[SQL]
update test set uname = N'a' where uname = N'b' and uid not in (select uid from test where uname = N'a')[Err] 1093 - You can't specify target table 'test' for update in FROM clause网上看了些类似的语句,用表别名什么的,由于本人技术有限,都没能改成功,请大侠帮忙改下,谢了
CREATE TABLE `test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`uid` varchar(255) NOT NULL,
`uname` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;INSERT INTO `test` VALUES ('1', '1', 'a');
INSERT INTO `test` VALUES ('2', '2', 'a');
INSERT INTO `test` VALUES ('3', '1', 'b');
INSERT INTO `test` VALUES ('4', '2', 'b');
INSERT INTO `test` VALUES ('5', '3', 'b');我想实现的操作是:
把uname=b,并且,uid不在a中的记录,uname更改为a
即更新的结果为:
id uid uname
1 1 a
2 2 a
3 1 b
4 2 b
5 3 a我是这样写的,但出错:update test set uname = N'a' where uname = N'b' and uid not in (select uid from test where uname = N'a')出错信息:
[SQL]
update test set uname = N'a' where uname = N'b' and uid not in (select uid from test where uname = N'a')[Err] 1093 - You can't specify target table 'test' for update in FROM clause网上看了些类似的语句,用表别名什么的,由于本人技术有限,都没能改成功,请大侠帮忙改下,谢了
解决方案 »
- my.ini配置出错Cann't connect to DB
- 如何解决TEXT类型列不能成为主健或主健组成部分的问题?
- 求MySql解析JSON格式的字符串或调用jar包解析也行
- 关于 mysql ibdata1文件的问题
- 请问mysql如何给字母数字混合的字段排序?
- SOS!!!我的mysql服务崩溃了。
- mysql启动的问题!
- 我在一台电脑上装上SQL SERVER 2000 的客户端,如何连上我所在局域网中的SQL SERVER 2000 服务器,
- 大家看下我这样检测主从数据是否一致的方法对不对?
- mysql中COUNT(*)与2次GROUP BY问题
- 两个表联合查询,有记录无法查询出来
- 求助各位一句难度非常高的MYSQL语句
INNER JOIN
(SELECT * FROM test2 a WHERE NOT EXISTS (SELECT 1 FROM test2 WHERE a.uid=uid AND
`uname`='a') ) b
ON a1.`id`=b.`id`
SET a1.`uname`='a';