Deleting Records from Table2 that appear in Table1

[HoboTraveler]

Hi All,

I have 2 tables (table1 and table2) that contain a list of usernames. I need to delete the records in table2 that appear in table1. Eg: If foo appears in table1 and table2, the entry needs to be deleted in table2.

I currently use the following code, that gets the usernames from table1 and deletes the entries from table2. However, I'm not sure if this is the best optimised way of working with tables containing 50,000 entries+. Also, the script below will attempt to delete the username even if it does not exist in table2...

Are there better methods of accomplishing this?

<?php

// Get the records from table1
$sql =
("
SELECT
username
FROM
table1
");

$result = mysql_query($sql);

while ($row = mysql_fetch_assoc ($result))
{
$username = $row['username'];

// Delete the username in table2
$sqldelete =
("
DELETE
FROM
table2
WHERE
username = '$username'
");

$sqldeleteResult = mysql_query($sqldelete);
}

?>

TIA

[eelixduppy]

I'm not a MySQL expert, but this query seems to work for me when I tested it out on very basic tables:

$query = "DELETE FROM table2 WHERE table2.colname = (SELECT colname FROM table1 WHERE colname = table2.colname)";

Hope this helps some. :)

Good luck!

[ahmedtheking]

How about this:

// get the rows from table1
$q = "SELECT id FROM table1";

while ($r = mysql_fetch_array(mysql_query($q),MYSQL_ASSOC))!== false) {

// check the ids in table2
$q2 = "SELECT id FROM table2 WHERE id = '".$r['id']."'";

while ($r2 = mysql_fetch_array(mysql_query($q),MYSQL_ASSOC))!== false) {

// delete them
mysql_query("DELETE FROM table2 WHERE id = '".$r2['id']."'");

}

}

That should do the trick!

[HoboTraveler]

@eelixduppy,

I tried your syntax, but mySQL gave the following error:

MySQL said:
#1242 - Subquery returns more than 1 row

Any ideas?

TIA

[siMKin]

use IN instead of =
DELETE FROM table2 WHERE table2.colname IN (SELECT colname FROM table1 WHERE colname = table2.colname)

[HoboTraveler]

@siMKin,

Excellent, the IN works great! If I may ask, what is the difference between = and IN..?

Also, I presume using this method to delete duplicates is quicker vs a delete through the while loop correct?

Thank-You

[jatar_k]

with IN you can pass it an array of values for any given column

= is used for a single value for any given column

[paul_owen]

If you use InnoDB type tables you can set up a foriegn key in the second table that points to the records in the first table that will do an automatic delete when the first table keys are deleted.

IE:

table_1 (
t1_id
stuff1
stuff2
etc...
)

table_2 (
t1_id
stuff...
foreign key( t1_id ) references table_1( t1_id )
on delete cascade
);

Or something similar to that...

Paul.