pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUPDATE FROM portability
4+ messages / 3 participants
[nested] [flat]
* UPDATE FROM portability
@ 2003-03-24 14:30 Andreas Pflug <Andreas.Pflug@web.de>
0 siblings, 1 reply; 4+ messages in thread
From: Andreas Pflug @ 2003-03-24 14:30 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: UPDATE FROM portability
@ 2003-03-26 10:27 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 4+ messages in thread
From: Christoph Haller @ 2003-03-26 10:27 UTC (permalink / raw)
To: pgsql-sql; +Cc: Andreas.Pflug@web.de
>
> 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 well
UPDATE 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: UPDATE FROM portability
@ 2003-03-26 18:16 Manfred Koizar <mkoi-pg@aon.at>
parent: Andreas Pflug <Andreas.Pflug@web.de>
0 siblings, 1 reply; 4+ messages in thread
From: Manfred Koizar @ 2003-03-26 18:16 UTC (permalink / raw)
To: Andreas Pflug <Andreas.Pflug@web.de>; +Cc: pgsql-sql
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 ...
Servus
Manfred
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: UPDATE FROM portability
@ 2003-03-26 19:08 Andreas Pflug <Andreas.Pflug@web.de>
parent: Manfred Koizar <mkoi-pg@aon.at>
0 siblings, 0 replies; 4+ messages in thread
From: Andreas Pflug @ 2003-03-26 19:08 UTC (permalink / raw)
To: Manfred Koizar <mkoi-pg@aon.at>; pgsql-sql
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
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-03-26 19:08 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-24 14:30 UPDATE FROM portability Andreas Pflug <Andreas.Pflug@web.de>
2003-03-26 18:16 ` Manfred Koizar <mkoi-pg@aon.at>
2003-03-26 19:08 ` Andreas Pflug <Andreas.Pflug@web.de>
2003-03-26 10:27 Re: UPDATE FROM portability Christoph Haller <ch@rodos.fzk.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox