Syntax to delete records SQL

Aleks
Aleks used Ask the Experts™
on
I have two tables:

userlogin  AND  users

The following query returns all records from the userlogin table, but some of the records do not have a matching record in the 'users' table. So because I am using a left join some records return a NULL value
How can I DELETE the records in the 'userlogin' table that don't have a matching record in the 'users' table.  ?  

SELECT a.UserLoginId, b.UserType, a.UserId, b.UserId AS usersuserid
FROM dbo.UserLogin a
LEFT JOIN users b ON b.UserId = a.UserId

Open in new window


This way I end up only with records that match one to one.

This is the list of records I want to delete:

SELECT a.UserLoginId, b.UserType, a.UserId, b.UserId AS usersuserid
FROM dbo.UserLogin a
LEFT JOIN users b ON b.UserId = a.UserId
WHERE b.userid IS NULL

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
DELETE FROM a
--SELECT a.UserLoginId, b.UserType, a.UserId, b.UserId AS usersuserid
FROM dbo.UserLogin a
LEFT JOIN users b ON b.UserId = a.UserId
WHERE b.userid IS NULL

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial