What would be the correct syntax, if any, that allows updating a table
variable in a statement that uses the same table variable in a correlated
subquery? Here's an example:

DECLARE @t table (N1 int NOT NULL, N2 int NOT NULL)

UPDATE @t SET
N1 = (SELECT COUNT(1)
FROM @t AS t
WHERE t.N2 < @t.N2)

This doesn't compile, complaining about "variable @t" in the WHERE clause.

I'm not so interested in a way to rewrite this particular statement to make
it work, but rather in a general way to refer to table variables in the
contexts where correlation names cannot be used.

Thank you.

--
remove a 9 to reply by email

Try this

UPDATE @t SET N1 = (SELECT COUNT(1) FROM @t AS t
WHERE t.N2 < M.N2) from M

Madhivanan

On Mar 24 2005, 04:20 am, "Madhivanan" <madhivanan2001@gmail.com> wrote in
news:1111656040.992951.161790@o13g2000cwo.googlegr oups.com:

>
> Try this
>
> UPDATE @t SET N1 = (SELECT COUNT(1) FROM @t AS t
> WHERE t.N2 < M.N2) from M
>
> Madhivanan
>

This gives

Server: Msg 208, Level 16, State 1, Line 3
Invalid object name 'M'.

If, however, I change the FROM clause above to

FROM @t AS M

then it compiles, but I'm not too familiar with UPDATE ... FROM to tell if
the statement will do what it's meant to do. In any case, is there a way to
do this that doesn't involve proprietory syntax?

--
remove a 9 to reply by email

>> In any case, is there a way to do this that doesn't involve
proprietory syntax? <<

The table variable is highly proprietary initself, so the best way is
not to use table variable at all. Then you can use Standard SQL UPDATE
statements easily and your code port, be maintainable, work the same
way from one release to the next, etc.

On Thu, 24 Mar 2005 12:43:40 -0000, Dimitri Furman wrote:

(snip)
>If, however, I change the FROM clause above to
>
>FROM @t AS M
>
>then it compiles, but I'm not too familiar with UPDATE ... FROM to tell if
>the statement will do what it's meant to do. In any case, is there a way to
>do this that doesn't involve proprietory syntax?

Hi Dimitri,

No, unfortunately there isn't. Of course, table variable are proprietary
themselves, so your code has to be adapted when you port it anyway - but
I admit that it would be nice if the standard syntax could be used.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Actually you can do this:

UPDATE @t SET N1 =
(SELECT COUNT(*)
FROM
(SELECT
N1 AS N3,
N2 AS N4
FROM @t)T
WHERE N4 < N2)

(I think I saw this suggestion from Steve Kass previously)

But as you suggest, it's pretty academic really.

--
David Portas
SQL Server MVP
--

On 24 Mar 2005 13:25:34 -0800, David Portas wrote:

>Actually you can do this:
(snip)

Hi David,

Neat!

>But as you suggest, it's pretty academic really.

Yeah ... academic, but neat nonetheless.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Dimitry

That shold be

UPDATE @t SET N1 = (SELECT COUNT(1) FROM @t AS t
WHERE t.N2 < M.N2) from @t M

Madhivanan