表结构如下: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网上看了些类似的语句,用表别名什么的,由于本人技术有限,都没能改成功,请大侠帮忙改下,谢了
解决方案 »
- java.sql.SQLException: Parameter index out of range (1 > number of parameters, ?
- mysql授了远程访问权限了 为何还会出现host is not allowed to connect to this mysql server
- [求助]换了nginx前端 后端 Apache 进不了后台了!
- 求一条sql语句
- 麻烦各位大虾帮我看看这个异常是为什么
- 求SQL语句,查每个用户最近一笔交易记录的金额
- 奇怪问题!!!
- 求教如何用JAVA在MYSQL中存取图片啊?
- Mysql 与Delphi6.0的相联?
- 急:大神帮忙,走过路过不要错过!MySQL 问题
- 两个表联合查询,有记录无法查询出来
- 求助各位一句难度非常高的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';