使用另一个表从表中删除记录？ [英] 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屋！