CODEWITHSUNDEEP
sqlselect
Mario
Mario

Reputation: 14780

SQL DELETE WITH JOIN not working

I have this query, which returns all records from Keywords which does not have a Quote The query is working, but I want to DELETE all these records, the problem is that if I put DELETE instead of SELECT I get errors.

SELECT Keywords.[Id]
  ,[QuoteId]      
  FROM [QuotesTemple].[dbo].[Keywords]
 LEFT JOIN [QuotesTemple].[dbo].Quotes ON Keywords.QuoteId=Quotes.Id
  WHERE Quotes.Id IS NULL

This does not work.

DELETE
  FROM [QuotesTemple].[dbo].[Keywords]
  LEFT JOIN [QuotesTemple].[dbo].Quotes ON Keywords.QuoteId=Quotes.Id
  WHERE Quotes.Id IS NULL

I get this error:

Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'LEFT'.

Upvotes: 1

Views: 909

Answers (3)

DevelopmentIsMyPassion
DevelopmentIsMyPassion

Reputation: 3591

you can also write as below

delete t1 FROM projects AS t1 LEFT OUTER JOIN [QuotesTemple].[dbo].Quotes AS t2 on t1.QuoteId= t2.QuoteId where t2.Id is Null

Upvotes: 1

veljasije
veljasije

Reputation: 7092

Valid syntax is:

DELETE [QuotesTemple].[dbo].[Keywords]
FROM [QuotesTemple].[dbo].[Keywords] AS k
LEFT JOIN [QuotesTemple].[dbo].[Quotes] AS q ON k.QuoteId = q.Id
WHERE q.Id IS NULL

Upvotes: 1

Luxspes
Luxspes

Reputation: 6750

Write it like this:

DELETE [QuotesTemple].[dbo].[Keywords] FROM [QuotesTemple].[dbo].[Keywords]
  LEFT JOIN [QuotesTemple].[dbo].Quotes ON Keywords.QuoteId=Quotes.Id
  WHERE Quotes.Id IS NULL

Upvotes: 2

Related Questions