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

In article <e320eda1.0202080734.1920821a_at_posting.google.com>, radevenz_at_ix.netcom.com (Richard A. DeVenezia) wrote:

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

If you want to verify that your SQL is standard compliant, check out the SQL Validator:

   http://developer.mimer.com/validator/index.tml

Regards,
Jarl

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

-- 
Jarl Hermansson
Mimer SQL Development team
http://developer.mimer.com