Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Delete rows from table based column values found in other table

Posted on 2008-10-04
4
Medium Priority
?
965 Views
Last Modified: 2013-12-12
I have a working web application with multiple tables.  I need to write a small PHP script that when it runs, deletes records from a table if the value of its first column is NOT found in a second table.

Let me explain.

I have two tables that this PHP script needs to compare, call them table1 and table2.  In each table there is a column called mid.  

I need a script that will delete a record in table1 if the value of the mid in table1 is not in table2, but if the value of the mid in table1 is in table2, the record is left untouched.

The values of mid is always numerical, such as 12345.

So the PHP script needs to look at all records in table1 (which may be one record or 1000 of records.  Note that after this PHP script runs, the remaining records are copied to another table and table1 is emptied).

Note that table2 does not have any duplicate values for mid.  So in table2, a value for mid is either present once or never.

So I need a PHP script that says this is the value of the mid in table1, if it is found in the table2 mid column, then the record in table1 stays.  If the value of the mid in table1 is NOT found in the mid column in table2, then the row in table1 is deleted.

0
Comment
Question by:fastfind1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 22641941
DELETE FROM table1
WHERE mid NOT IN (SELECT mid FROM table2)

OR

DELETE FROM table1
WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table2.mid = table1.mid)

Either of those should function for what you need to do.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22641953
If you are asking for the PHP code as well, take a look at this:
http://www.php-mysql-tutorial.com/mysql-update-and-delete.php.

You would just change $query to this:
$query = "DELETE FROM table1 WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table2.mid = table1.mid)";

Hope that helps.

Regards,
Kevin
0
 

Author Comment

by:fastfind1
ID: 22657734
Can you help me understand SELECT 1 FROM?  I understand SELECT mid FROM table2.  But I don't understand SELECT 1 FROM table2.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22658082
You can use SELECT mid FROM table2 there as well, but since we are checking where this record doesn't exist at all using "FROM table2 WHERE table2.mid = table1.mid" then it doesn't matter what we sellect as we are just checking for a row.  We could SELECT NULL here and it would work the same.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question