How to use CTE

I use CTE of VS2005:
WITH AA (County,Town ) AS
(
SELECT County,Town
FROM Zip where zip='413'
)
But it result an Error:
Msg 102, Level 15, State 1, Line 6 Incorrect syntax near ')'.
What's the problems?
How can I do ?
After defining the CTE, you should use it in another SQL statement.
Try something like this:
WITH AA (County,Town ) AS
(
SELECT County,Town
FROM Zip where zip='413'
)
SELECT * FROM aa
Razvan
|||Thanks,
It can run.
Is the CTE table can be used only once?
It fail if I add tow statement after defing the CTE:
WITH AA (County,Town ) AS
(
SELECT County,Town
FROM Zip where zip='413'
)
update AA SET County ='another'
Select * from AA
"Razvan Socol" <rsocol@.gmail.com>
??:1131894130.133010.143350@.g44g2000cwa.googl egroups.com...
> After defining the CTE, you should use it in another SQL statement.
> Try something like this:
> WITH AA (County,Town ) AS
> (
> SELECT County,Town
> FROM Zip where zip='413'
> )
> SELECT * FROM aa
> Razvan
>
|||> Is the CTE table can be used only once?
Yes, the execution scope of a CTE is a single SELECT, INSERT, UPDATE, or
DELETE statement.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:epsTOlI6FHA.1864@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> It can run.
> Is the CTE table can be used only once?
> It fail if I add tow statement after defing the CTE:
> WITH AA (County,Town ) AS
> (
> SELECT County,Town
> FROM Zip where zip='413'
> )
> update AA SET County ='another'
> Select * from AA
>
>
> "Razvan Socol" <rsocol@.gmail.com>
> ??:1131894130.133010.143350@.g44g2000cwa.googl egroups.com...
>