Thread: UPDATE FROM portability

Updating some rows in tab1 with corresponding values from tab2, pgsql style:

UPDATE tab1         SET value=T2.VALUE    FROM tab2 T2 WHERE T2.restr=1       AND tab1.key=T2.key
<<<<<<

The same for MSSQL:

UPDATE tab1         SET value=T2.VALUE    FROM tab1 T1       JOIN tab2 T2 ON T1.key=T2.Key       <<<<<< WHERE
T2.restr=1


I'm looking for a portable query style, without using a subquery in the 
SET clause (which could make things quite slow)
Any hints?

Regards,
Andreas

>
> Updating some rows in tab1 with corresponding values from tab2, pgsql
style:
>
> UPDATE tab1
>           SET value=T2.VALUE
>      FROM tab2 T2
>   WHERE T2.restr=1
>         AND tab1.key=T2.key                <<<<<<
>
> The same for MSSQL:
>
> UPDATE tab1
>           SET value=T2.VALUE
>      FROM tab1 T1
>         JOIN tab2 T2 ON T1.key=T2.Key       <<<<<<
>   WHERE T2.restr=1
>
>
> I'm looking for a portable query style, without using a subquery in
the
> SET clause (which could make things quite slow)
> Any hints?
>
In postgres this should work as wellUPDATE tab1          SET value=tab2.VALUE  WHERE tab2.restr=1        AND
tab1.key=tab2.key
I have to admit I don't know nothing about mssql,
so I have no idea if this is of any real help.

Regards, Christoph

On Mon, 24 Mar 2003 15:30:45 +0100, Andreas Pflug
<Andreas.Pflug@web.de> wrote:
>Updating some rows in tab1 with corresponding values from tab2, pgsql style:
>UPDATE tab1
>          SET value=T2.VALUE
>     FROM tab2 T2
>  WHERE T2.restr=1
>        AND tab1.key=T2.key

AFAIK this should work with MSSQL7.  Assuming you didn't name your
columns value and key ...

ServusManfred

Thanks Manfred,

you're right. If written without typo (ahem...:-) MSSQL will execute the 
pgsql syntax correctly.

>AFAIK this should work with MSSQL7.  Assuming you didn't name your
>columns value and key ...
>
>Servus
> Manfred
>
>  
>