使用另一个表从表中删除记录? [英] delete records from table using another table?
问题描述
注意:对编辑:请编辑标题,如果有更好的一个:)
我的问题是:
$ b b
我的数据库中有两个表
-----------
| table1 |
| ---------- |
| id |
| text |
===========
-----------
| table2 |
| ---------- |
| id |
| text |
===========
table1是600,000条记录
table2是5,000,000条记录!!:)
什么是删除table2中所有记录的最佳方法不在table1
我主要的方式是最快的方式,因为我不想等待4个小时来完成该过程
您有比以下代码更好的东西:
<?PHP
$ sql =从table2中选择文本;
$ result = mysql_query($ sql)或die(mysql_error());
while($ row = mysql_fetch_array($ result)){
$ text = $ row [text];
$ sql2 =select id from table1 where text ='$ text';
$ query2 = mysql_query($ sql2)或die(mysql_error());
$ result2 = mysql_num_rows($ query2);
if($ result2 == 0){
$ sql3 =delete from table2 where text ='$ text';
$ query3 = mysql_query($ sql3)或die(mysql_error());
}
}
?>感谢解决方案 div> 怎么让RDBM处理呢?
例如
DELETE FROM table2 WHERE text NOT IN从表1的不同文本)
Cheers
PS:在测试之前做一些备份...
note: to the editors: please edit the title if have a better one :)
my question is:
I have two tables in my database
-----------
| table1 |
|----------|
| id |
|text |
===========
-----------
| table2 |
|----------|
| id |
|text |
===========
table1 is 600,000 records
table2 is 5,000,000 records !!:)
what is the best way to delete all the records in table2 that are not in table1
I main by the way -the fastest way because I don't want to wait 4 hours to complete the process
do you have something better than the following code:
<?PHP
$sql = "select text from table2";
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_array($result)){
$text = $row["text"];
$sql2 = "select id from table1 where text = '$text'";
$query2 = mysql_query($sql2) or die(mysql_error());
$result2 = mysql_num_rows($query2);
if($result2==0){
$sql3 = "delete from table2 where text = '$text'";
$query3 = mysql_query($sql3) or die(mysql_error());
}
}
?>
Thanks
解决方案 what about letting the RDBM handle it ?
for example
DELETE FROM table2 WHERE text NOT IN (select distinct text from table1)
Cheers
PS: do some backup before testing ...
这篇关于使用另一个表从表中删除记录?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文