有俩个表table1 , table2talbe1 有俩个字段id,pid 有2千万条记录 同一pid对应多个id
table2 有一个字段pid 有100万条记录 pid唯一想要删除表table1中记录,条件是table1.pid=table2.pid我想有几种方法实现表关联的删除发现
delete a from a join b ....
delete a from xxx a where ....
之类的语法在mysql都行不通。。
table2 有一个字段pid 有100万条记录 pid唯一想要删除表table1中记录,条件是table1.pid=table2.pid我想有几种方法实现表关联的删除发现
delete a from a join b ....
delete a from xxx a where ....
之类的语法在mysql都行不通。。
delete a
from vim a join vim2 b on ...好像只有 delete a,b
from vim a join vim2 b on ...我只要删除单表的...
那样的需求 貌似mysql也没好的写法...感觉这里mysql支持的太少了~
1,2 union all select
1,3 union all select
2,4 union all select
2,3 ;delete a,b from vm1 a,(select max(value) as v ,id from vm1 group by id ) b
where a.id=b.id and a.value<>b.value;
---这样的语法都报错了。。发愁啊 这个删除功能不怎么样啊
delete a from a inner join b ...
在MYSQL是可以的
delete from a
where exists(select ... 这里使用a的列是错误的)
是这么理解吧?
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.1.21-communityType 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql> use tempdb
Database changed
mysql> drop table a,b;
ERROR 1051 (42S02): Unknown table 'a,b'
mysql> create table a (a int , b datetime);
Query OK, 0 rows affected (0.06 sec)mysql> insert into a select 1 ,NOW() -3;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 1mysql> insert into a select 2 ,NOW() -2;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 1mysql> insert into a select 3 ,NOW() -1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 1mysql> insert into a select 3 ,NOW() -0.5;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 1mysql> insert into a select 3 ,NOW();
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql>
mysql> create table b (aa int , bb varchar(2));
Query OK, 0 rows affected (0.07 sec)mysql> insert into b select 1 ,'a';
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> insert into b select 2 ,'b';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> insert into b select 3 ,'c';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> insert into b select 3 ,'c';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> insert into b select 3 ,'c';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> select * from a;
+------+---------------------+
| a | b |
+------+---------------------+
| 1 | 0000-00-00 00:00:00 |
| 2 | 0000-00-00 00:00:00 |
| 3 | 0000-00-00 00:00:00 |
| 3 | 0000-00-00 00:00:00 |
| 3 | 2010-08-09 13:54:29 |
+------+---------------------+
5 rows in set (0.00 sec)mysql> select * from b;
+------+------+
| aa | bb |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 3 | c |
| 3 | c |
+------+------+
5 rows in set (0.00 sec)mysql> select * from a join b on a.a=b.aa where b.bb='a' and a.b='0000-00-00 00:
00:00';
+------+---------------------+------+------+
| a | b | aa | bb |
+------+---------------------+------+------+
| 1 | 0000-00-00 00:00:00 | 1 | a |
+------+---------------------+------+------+
1 row in set (0.00 sec)mysql> delete a from a join b on a.a=b.aa where b.bb='a' and a.b='0000-00-00 00:
00:00';
Query OK, 1 row affected (0.01 sec)mysql> select * from a;
+------+---------------------+
| a | b |
+------+---------------------+
| 2 | 0000-00-00 00:00:00 |
| 3 | 0000-00-00 00:00:00 |
| 3 | 0000-00-00 00:00:00 |
| 3 | 2010-08-09 13:54:29 |
+------+---------------------+
4 rows in set (0.00 sec)mysql>