i have this sql Q:

a delete statement not allowed to use with "as" (delete from table as t
where..),
so when i need "as" i do it in sub q, like this:

delete from customers where 3<(select count(*) from customer t where
t.date=customer.date)

but when table in memory table, I got err:

declare @t table([id] int, [date] smalldatetime)
delete from @t where 3<(select count(*) from @t t where @t.date=t.date)


I got err:Must declare the scalar variable "@t"

have a solution for like situations?

Indeed, you need to use an alias. Try something like this:

declare @t table([id] int, [date] smalldatetime)
delete a from @t a where 3<(select count(*) from @t t where
a.date=t.date)

Razvan

Here is another way, without aliases:

DELETE @t WHERE date IN (
  SELECT date FROM @t
  GROUP BY date
  HAVING COUNT(*)>3
)

Razvan

PS. I hope your real columns have better names...

Your attempt at inventing syntax  makes no sense in terms of the SQL
language model.  An alias is supposed to act as it materializes a new
working table with the data from the original table expression in it.
To be consistent, this syntax says that you have done nothing to the
base table.

The next question is why would you use that proprietary in memory table
in the first place? It looks like you are mimicking a scratch tape in a
1950's file system instead of writing SQL.  But you did not post enough
for anyone to give you a relatioanl solution.

to --CELKO--
I realy interest you, but i not understand at all, please explain your
approach!!

for anyone to give you a relatioanl solution.

my example is very clear, i need to delete from this table rec that
appear more
then 3 times. did you have better way?

Use an alias, but use it where it belongs:

delete    <alias>
    from    <table> [as] <alias>
    where    <condition>


ML

---
http://milambda.blogspot.com/

Oracle MINUS equivalent in MSSQL2000 ?
unique records
column info from temp table
Query slow with hardcoded dates
TSQL - Address cleaning
Best Practice
special caracters
some simple proc help
Advance TSQL question
wsid() help