too long

Question

How would I run a code where it checks table1 for the column postid and then searches table2 to see if that value actually exists. If it does exist then move onto the next but if it doesn't it removes the row from table1.

So if in table1 I have a row which has 3 rows: a postid of 333 another of 444 and another of 555.

Let's say 333 doesn't exist in table2 then I need to delete the row which contains that postid from table1 and move onto the next. It keeps doing that until all the rows are checked.

How would I accomplish this? I am very confused and haven’t been able to think of everything.

Answer 1

If you're iterating the post ids, you can perform this type of delete for each postid:

DELETE FROM table1 WHERE postid = 333 AND 333 NOT IN (SELECT postid FROM table2 WHERE postid = 333);

Or, delete all rows from table1 that does not have a record with a matching postid in table2 (assuming table2.postid column is defined as NOT NULL):

DELETE table1 FROM table1 LEFT JOIN table2 ON (table2.postid = table1.postid) WHERE table2.postid IS NULL;