alias for variable [memory] table?

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|||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.|||Use an alias, but use it where it belongs:
delete <alias>
from <table> [as] <alias>
where <condition>
ML
http://milambda.blogspot.com/|||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...|||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?sql