Select and Delete SQL entries from one table that don’t have a relational partner in another one

I have two tables inv_computer and inv_user and want to find and delete all computers that don’t have an associated user.

SELECT * FROM `inv_computer` AS c LEFT OUTER JOIN inv_user AS u ON c.ownerid=u.id WHERE u.id IS NULL 

and

DELETE c FROM `inv_computer` AS c LEFT OUTER JOIN inv_user AS u ON c.ownerid=u.id WHERE u.id IS NULL
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s