Date:         Thu, 17 Aug 2006 11:48:46 -0700
Reply-To:     jfh@stanfordalumni.org
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jack Hamilton <jfh@STANFORDALUMNI.ORG>
Subject:      Re: update statement in proc sql doesn't make sense to me
Comments: To: "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
In-Reply-To:  <200608170304.k7GMGaWv027301@mailgw.cc.uga.edu>
Content-Type: text/plain; charset=iso-8859-1

As an alternative to using two queries, you could use

=====
   update one as outer
      set value1 =
            coalesce(
               (select inner.value2 as value1
                from   two as inner
                where  outer.key=inner.key),
                outer.value1);
=====

You skip a subquery, but you update every row in the outer table.
Which method is more efficient probably depends on the percentage of
rows which need to be updated, and probably some SQL optimization which
is somewhat out of our control.


--- "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM> wrote:

> On Wed, 16 Aug 2006 15:38:44 -0700, Jack Hamilton
> <jfh@STANFORDALUMNI.ORG>
> wrote:
>
> >Yes, it would be convenient for your code to work.  Perhaps it
> doesn't
> >follow some SQL standard, or perhaps SAS just decided not to
> implement
> >it.
> >
> >You could use
> >
> >=====
> >proc sql;
> >   create table one (key char(1), value1 float);
> >   create table two (key char(1), value2 float);
> >   insert into one(key, value1)
> >            values('a', .)
> >            values('b', 3);
> >   insert into two(key, value2)
> >            values('a', 10);
> >   update one as outer
> >      set value1 =
> >         (select inner.value2 as value1
> >          from   two as inner
> >          where  outer.key=inner.key)
> >         where outer.key = any (select key from two);
> >quit;
> >=====
> >
> >I'm afraid someone else will have to explain why the outer WHERE
> clause
> >is necessary.
>
> You generally need the two subqueries as Jack showed, but the code
> can be
> whittled down a bit to something like
>
>    update one as outer
>      set value1 = (select value2 from two where key=outer.key)
>        where key in (select key from two);
>
> In the absence of the outer WHERE, any row in ONE for which a value
> is *not*
> returned by the first subquery will have VALUE1 set to null
> (missing). If
> you want that to happen, or if you are certain that TWO will provide
> a new
> value for every row in ONE, you can omit the outer WHERE.
>
> >
> >
> >--- Wensui Liu <liuwensui@GMAIL.COM> wrote:
> >
> >> I just feel very uncomfortable with update statement. In t-sql, I
> can
> >> do
> >> something like:
> >>
> >> update table1
> >> set x = table2.x
> >> from table2
> >> where table1.key = table2.key
> >>
> >> But I can't do the above using update statement in proc sql. Any
> idea
> >> about
> >> it?
> >>
> >> --
> >> WenSui Liu
> >> (http://spaces.msn.com/statcompute/blog)
> >> Senior Decision Support Analyst
> >> Health Policy and Clinical Effectiveness
> >> Cincinnati Children Hospital Medical Center
> >>
> >
> >
> >---
> >Jack Hamilton
> >Sacramento, California
>


---
Jack Hamilton
Sacramento, California