...

View Full Version : Comparing two tables and delete rows



InsaneRhino
03-25-2007, 01:19 PM
Hello everyone.

I have two tables pluserdata and pluserlids. pluserdata contains all the users information such as ID username password email etc, and pluserlids contains just corresonding IDs (from pluserdata) and then leagueid which correspods to another table.

What I am after is a query to look at pluserlids and then delete any rows that do not have a corresonding id in pluserdata.

Another way of explaining it is that I have deleted about 1000 users (spam) from pluserdata but their ids are still in the pluserlids table so I want to check to see if they have been deleted then to remove their lid from pluserlids.

Much thanks.

Ryan


EDIT: oh and I'm using MySQL 4.0.20

Fumigator
03-25-2007, 06:22 PM
You can do an outer join on the two tables and where you find NULL, that's an orphaned row.

This thread (http://codingforums.com/showthread.php?t=108642)has a code example-- you would want to turn it into a DELETE query (but maybe do it as a SELECT query first to make sure the results are what you want to actually delete ;) )

InsaneRhino
03-26-2007, 09:56 AM
Thank you very much my friend, I used the code below and it worked a treat. Much love.

DELETE pluserlids. * FROM pluserlids LEFT JOIN pluserdata ON pluserlids.userid = pluserdata.userid WHERE pluserdata.userid IS NULL



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum