问个问题
如果一个表A 其中有两个字段 id 和 del
都不是主键，我想作这样的操作：
如果id=1的记录 del都是１ 则 删除所有id=1的记录 ．
否则都不删除
我这样写了
delete from A
where id=1
and (select count(*) from A where id=1)
=(select count(*) from A where id=1 and del=1)
这样写 Mysql会报错
正确的应该怎么写 ？
[解决办法]
mysql> create table table1( -> id int primary key, -> name varchar(10), -> code int, -> del int -> );Query OK, 0 rows affected (0.09 sec)mysql> insert into table1 value(1,'jia',1,1) ;Query OK, 1 row affected (0.28 sec)mysql> insert into table1 value(2,'jia',1,1) ;Query OK, 1 row affected (0.30 sec)mysql> insert into table1 value(3,'jia',1,0);Query OK, 1 row affected (0.28 sec)mysql> select * from table1;+----+------+------+------+| id | name | code | del |+----+------+------+------+| 1 | jia | 1 | 1 || 2 | jia | 1 | 1 || 3 | jia | 1 | 0 |+----+------+------+------+3 rows in set (0.00 sec)mysql> insert into table1 value(1,'jia2',2,1) ;ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'mysql> insert into table1 value(2,'jia2',2,1) ;ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'mysql> insert into table1 value(3,'jia2',2,1);ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'mysql> insert into table1 values(1,'jia2',2,1) ;ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'mysql> insert into table1 values(2,'jia2',2,1) ;ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'mysql> insert into table1 values(3,'jia2',2,1);ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'mysql> insert into table1 values(11,'jia2',2,1) ;Query OK, 1 row affected (0.06 sec)mysql> insert into table1 values(12,'jia2',2,1) ;Query OK, 1 row affected (0.03 sec)mysql> insert into table1 values(13,'jia2',2,1);Query OK, 1 row affected (0.03 sec)mysql> select * from table1;+----+------+------+------+| id | name | code | del |+----+------+------+------+| 1 | jia | 1 | 1 || 2 | jia | 1 | 1 || 3 | jia | 1 | 0 || 11 | jia2 | 2 | 1 || 12 | jia2 | 2 | 1 || 13 | jia2 | 2 | 1 |+----+------+------+------+6 rows in set (0.00 sec)mysql> delete a from table1 a, ( -> select code from table1 t where not exists (select 1 from table1 where code=t.code and del!=1) -> ) b -> where a.code=b.code;Query OK, 3 rows affected (0.08 sec)mysql> select * from table1;+----+------+------+------+| id | name | code | del |+----+------+------+------+| 1 | jia | 1 | 1 || 2 | jia | 1 | 1 || 3 | jia | 1 | 0 |+----+------+------+------+3 rows in set (0.00 sec)mysql>
