Q: Does any ANSI-SQL standard address multi-column updates ?

From: Richard A. DeVenezia <radevenz_at_ix.netcom.com>
Date: 8 Feb 2002 07:34:13 -0800
Message-ID: <e320eda1.0202080734.1920821a@posting.google.com>

Does anyone have information or a site where I can learn more about which ANSI-SQL standard or level formalizes/blesses/requires multi-column updates using multi-column lookup?

This works in Oracle but not in SAS/SQL I am working with.

Suppose I have a BASE and LOOKUP table
BASE: x num, y num, a char, b char
LOOKUP: x num, y num, a char, b char

and I want to do

update BASE outer
set (a,b) = (select a,b from LOOKUP inner

             where outer.x=inner.x
               and outer.y=inner.y)

where
exists (select a,b from LOOKUP inner

             where outer.x=inner.x
               and outer.y=inner.y)

Am I wrong to hope for an even simpler syntax ?

Something like

update
  TABLE1(column1,...,columnN)[keyColumn1,...,keyColumnM] from
  TABLE2(kolumn1,...,kolumnN)[keyKolumn1,...,keyKolumnM]

(where the TABLE2 would not need all the extra stuff if the column names matched) Received on Fri Feb 08 2002 - 09:34:13 CST