"一下操作",估计你的意思是想一条sql语句就完成.
这个......想不出来
这个......想不出来
解决方案 »
- mysql workbench报错
- MySQL left join 很慢
- 存储过程,变量处理方式,自定义显示列
- 安装了mysql-refman-5.-en-html-chapter-1-.noarch.rpm,从哪里打开?
- mysql问题,高手进!
- MYSQL高手过来帮解决一个行列互转的问题
- mysql 安装问题
- 创建表的时候里面有一个字段的类型是LongBlob请问插入sql语句应该如何写
- Navicat for MySQL中导入CSV文件出现问题
- load data infile 如何批量导入多个txt文件
- 帮忙!!ERROR 1064 (42000): You have an error in your SQL syntax;
- 求一条查询语句,列名:test(varchar).我想查找列出符合该字段中所有包含"good"的子串;
可以用多条sql语句。
select命令下,我做过类似的事情,但问题是在update命令中,可能会有些其他限制。楼主可以参考一下我的一个问题:
http://community.csdn.net/Expert/TopicView3.asp?id=4139675
表结构如下:
mysql> desc test;
+---------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| id | varchar(10) | | | | |
| state | varchar(10) | | | | |
| num | int(10) unsigned | | | 0 | |
| msg | varchar(45) | | | | |
| records | int(10) unsigned | | PRI | 0 | |
+---------+------------------+------+-----+---------+-------+
写入三条记录后如下:+---------+---------+-----+-----+---------+
| id | state | num | msg | records |
+---------+---------+-----+-----+---------+
| 6004629 | SUCCESS | 10 | 1 | 1 |
| 6004629 | SUCCESS | 10 | 2 | 2 |
| 6004629 | SUCCESS | 10 | 5 | 3 |
+---------+---------+-----+-----+---------+mysql> update test set num='0' where id='6004629' and records>1;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0mysql> select * from test;
+---------+---------+-----+-----+---------+
| id | state | num | msg | records |
+---------+---------+-----+-----+---------+
| 6004629 | SUCCESS | 10 | 1 | 1 |
| 6004629 | SUCCESS | 0 | 2 | 2 |
| 6004629 | SUCCESS | 0 | 5 | 3 |
+---------+---------+-----+-----+---------+mysql> update test set num='0' where id='6004629' and
-> records>(select min(records) from test where id='6004629');
ERROR 1093 (HY000): You can't specify target table 'test' for update in FROM cla
use
可能update语句不支持子查询?
mysql> select min(records) from test where id='6004629';
+--------------+
| min(records) |
+--------------+
| 1 |
+--------------+
如果真的改的话,可以手动,如果你不怕麻烦。呵呵!
#
# Host: 192.168.0.200 Database: test1
# --------------------------------------------------------
# Server version 4.0.18-nt
#
# Table structure for table 'test'
#CREATE TABLE test (
id varchar(10) NOT NULL default '',
state varchar(10) NOT NULL default '',
num int(10) unsigned NOT NULL default '0',
msg varchar(50) NOT NULL default ''
) TYPE=MyISAM;#
# Dumping data for table 'test'
#INSERT INTO test VALUES("6004629", "SUCCESS", "10", "TEXT");
INSERT INTO test VALUES("6004629", "SUCCESS", "10", "TEXT");
INSERT INTO test VALUES("6004629", "SUCCESS", "10", "TEXT");
INSERT INTO test VALUES("6007210", "SUCCESS", "400", "TEXT");
INSERT INTO test VALUES("6007210", "SUCCESS", "400", "TEXT");
INSERT INTO test VALUES("6007210", "FAILED", "400", "TEXT");#
# select test data
#
select * from test;#
# add a idno and add primary key
#
ALTER TABLE `test` ADD `idno` INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(id,idno);#
# select test data
#
select * from test;#
# update test
#
update test set num=0 where idno!=1;#
# drop primary key & idno
#
ALTER TABLE `test` DROP PRIMARY KEY, DROP idno;#
# select test data
#
select * from test;
mysql> # MySQL-Front Dump 2.5
mysql> #
mysql> # Host: 192.168.0.200 Database: test1
mysql> # --------------------------------------------------------
mysql> # Server version 4.0.18-nt
mysql>
mysql>
mysql> #
mysql> # Table structure for table 'test'
mysql> #
mysql>
mysql> CREATE TABLE test ( id varchar(10) NOT NULL default '', state varchar(10) NOT NULL default '', num int(10) unsigned NOT NULL default '0', msg varchar(50) NOT NULL default '') TYPE=MyISAM;
Query OK, 0 rows affected (0.05 sec)mysql>
mysql>
mysql>
mysql> #
mysql> # Dumping data for table 'test'
mysql> #
mysql>
mysql> INSERT INTO test VALUES("6004629", "SUCCESS", "10", "TEXT");
Query OK, 1 row affected (0.02 sec)mysql> INSERT INTO test VALUES("6004629", "SUCCESS", "10", "TEXT");
Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO test VALUES("6004629", "SUCCESS", "10", "TEXT");
Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO test VALUES("6007210", "SUCCESS", "400", "TEXT");
Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO test VALUES("6007210", "SUCCESS", "400", "TEXT");
Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO test VALUES("6007210", "FAILED", "400", "TEXT");
Query OK, 1 row affected (0.00 sec)mysql>
mysql> #
mysql> # select test data
mysql> #
mysql> select * from test;
+---------+---------+-----+------+
| id | state | num | msg |
+---------+---------+-----+------+
| 6004629 | SUCCESS | 10 | TEXT |
| 6004629 | SUCCESS | 10 | TEXT |
| 6004629 | SUCCESS | 10 | TEXT |
| 6007210 | SUCCESS | 400 | TEXT |
| 6007210 | SUCCESS | 400 | TEXT |
| 6007210 | FAILED | 400 | TEXT |
+---------+---------+-----+------+
6 rows in set (0.00 sec)mysql>
mysql> #
mysql> # add a idno and add primary key
mysql> #
mysql> ALTER TABLE `test` ADD `idno` INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(id,idno);
Query OK, 6 rows affected (0.10 sec)
Records: 6 Duplicates: 0 Warnings: 0mysql>
mysql> #
mysql> # select test data
mysql> #
mysql> select * from test;
+---------+---------+-----+------+------+
| id | state | num | msg | idno |
+---------+---------+-----+------+------+
| 6004629 | SUCCESS | 10 | TEXT | 1 |
| 6004629 | SUCCESS | 10 | TEXT | 2 |
| 6004629 | SUCCESS | 10 | TEXT | 3 |
| 6007210 | SUCCESS | 400 | TEXT | 1 |
| 6007210 | SUCCESS | 400 | TEXT | 2 |
| 6007210 | FAILED | 400 | TEXT | 3 |
+---------+---------+-----+------+------+
6 rows in set (0.01 sec)mysql>
mysql> #
mysql> # update test
mysql> #
mysql> update test set num=0 where idno!=1;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0mysql>
mysql> #
mysql> # drop primary key & idno
mysql> #
mysql> ALTER TABLE `test` DROP PRIMARY KEY, DROP idno;
Query OK, 6 rows affected (0.10 sec)
Records: 6 Duplicates: 0 Warnings: 0mysql>
mysql> #
mysql> # select test data
mysql> #
mysql> select * from test;
+---------+---------+-----+------+
| id | state | num | msg |
+---------+---------+-----+------+
| 6004629 | SUCCESS | 10 | TEXT |
| 6004629 | SUCCESS | 0 | TEXT |
| 6004629 | SUCCESS | 0 | TEXT |
| 6007210 | SUCCESS | 400 | TEXT |
| 6007210 | SUCCESS | 0 | TEXT |
| 6007210 | FAILED | 0 | TEXT |
+---------+---------+-----+------+
6 rows in set (0.01 sec)mysql> exit