Delete left join query 1

[44nato44]

Hi

I made this query in MS Access database but have not imported my database to MS SQL but how can I get this query to work.

Delete tblAccessRights.*
From [tblAccessRights]
Left Join [tblTriSecActis]
on tblAccessRights.UserId = tblTriSecActis.UserName
Where tblTriSecActis.UserName Is NULL


I hope somebody can help me

 

[gmmastros]

Your syntax is very close. All you need to do is remove the .* part.

Delete tblAccessRights
From [tblAccessRights]
Left Join [tblTriSecActis]
on tblAccessRights.UserId = tblTriSecActis.UserName
Where tblTriSecActis.UserName Is NULL

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

If you wanted to delete all records that do not exist in the other table, this syntax will be better:

delete from tblAccessRights 
where not exists (select 1 from tblTriSecActis SA
where SA.UserName = tblAccessRights.UserID)

Read this very entertaining blog as to why
Dear From Clause


PluralSight Learning Library

 

[44nato44]

great thanks a lot