X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 50BD6475CBC
	for <pgsql-sql@postgresql.org>; Wed, 26 Mar 2003 05:27:17 -0500 (EST)
Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52])
	by postgresql.org (Postfix) with SMTP id E68DA474E44
	for <pgsql-sql@postgresql.org>; Wed, 26 Mar 2003 05:27:14 -0500 (EST)
Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ;
 Wed Mar 26 11:27:15 2003 +0100
Received: œby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id LAA20092; Wed, 26 Mar 2003 11:27:14 +0100 (MET)
Message-ID: <3E818082.FFE8E2C7@rodos.fzk.de>
Date: Wed, 26 Mar 2003 11:27:14 +0100
From: Christoph Haller <ch@rodos.fzk.de>
X-Mailer: Mozilla 4.77 [en] (X11; U; HP-UX B.10.20 9000/780)
X-Accept-Language: en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Cc: Andreas.Pflug@web.de
Subject: Re: UPDATE FROM portability
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit
X-Spam-Status: No, hits=-10.4 required=5.0
	tests=BAYES_01,QUOTED_EMAIL_TEXT,QUOTE_TWICE_1
	autolearn=ham	version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200303/578
X-Sequence-Number: 12616

>
> 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