January 29, 2010 at 10:58 am
Hi I am receiving a syntax error message for the query below...
I can run this as a select statement ie select * from... but when I try to delete I'm getting a syntax error and can't figure out why.
Thanks for any help in advance...
Incorrect syntax near the keyword 'as'
delete
from
table1 as source
where
exists
(
select id
from table2 as dest
where dest.id = source.id
)
January 29, 2010 at 11:23 am
Assuming you are trying to delete from table1, try this:
delete from
dbo.table1
from
dbo.table1 as source
where
exists
(
select id
from dbo.table2 as dest
where dest.id = source.id
January 29, 2010 at 12:09 pm
Thanks Lynn that worked...Not sure why a double 'from' was needed, is there a better way to write something like this?
January 29, 2010 at 12:31 pm
delete
dbo.table1
from dbo.table1 as source
Inner Join dbo.table2 as dest
on dest.id = source.id
Is an alternative method to write it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 29, 2010 at 1:11 pm
Thank you...
January 29, 2010 at 2:46 pm
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 31, 2010 at 5:33 am
Or even:
DELETE dbo.Table1 WHERE id = ANY (SELECT D.id FROM dbo.Table2 D);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 1, 2010 at 7:47 am
Or this:
delete
from
table1
where
exists
(
select id
from table2 as dest
where dest.id = table1.id
)
Just lose the "AS."
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply