Query optimize

can this be rewritten in more optimized way..
DELETE FROM A WHERE ID Not In (SELECT ID FROM @.ABC)DELETE FROM A T WHERE ID Not exists (SELECT ID FROM @.ABC where ID=T.ID)
Madhivanan|||You cannot use a variable table name. Indexes will help, but the
optimizer is msart enough to pick a good plan.|||You could possibly try:
DELETE def
WHERE NOT EXISTS
(
SELECT 1
FROM abc
WHERE abc.ID = def.ID
)
Frank
"Sunny" <Sunny@.discussions.microsoft.com> wrote in message
news:1BF75571-9841-41EC-95EF-16D6837095C9@.microsoft.com...
> can this be rewritten in more optimized way..
> DELETE FROM A WHERE ID Not In (SELECT ID FROM @.ABC)|||delete A
from A
left join B
on B.CommonKey = A.CommonKey
where (B.CommonKey is null)
ML|||On Thu, 11 Aug 2005 06:54:01 -0700, Sunny wrote:

> can this be rewritten in more optimized way..
> DELETE FROM A WHERE ID Not In (SELECT ID FROM @.ABC)
As Celko said, you can't use a variable table name directly. The best you
can do is dynamic SQL:
declare @.abc nvarchar(32)
declare @.sql nvarchar(500)
set @.abc='MyTable'
set @.sql = N'DELETE FROM A WHERE NOT EXISTS (SELECT * FROM ' + @.ABC + ' B
WHERE A.ID=B.ID)'
execute sp_executesql @.sql|||Ah, I must have just breezed through the DML statement. I didn't catch the
@.. Sunny, if you were using a declared table variable by design, you should
follow the advice in the posts that followed mine.
Frank Castora.
"Sunny" <Sunny@.discussions.microsoft.com> wrote in message
news:1BF75571-9841-41EC-95EF-16D6837095C9@.microsoft.com...
> can this be rewritten in more optimized way..
> DELETE FROM A WHERE ID Not In (SELECT ID FROM @.ABC)|||Before you issue blanket statements like the one below, you should check
your facts. You can indeed use a variable table name, provided the variable
table name refers to an existing table variable. Table variables are a
feature of SQL Server 2000. You refer to a table variable in the same way
as a scalar variable.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1123769246.878870.169080@.g44g2000cwa.googlegroups.com...
> You cannot use a variable table name. Indexes will help, but the
> optimizer is msart enough to pick a good plan.
>