Date:         Wed, 18 Mar 2009 15:39:20 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: proc sql update
Comments: To: Tiffany <tiffany.vidal@GMAIL.COM>
In-Reply-To:  <1a5de901-23fe-4d15-b0bf-5e4f7531884c@w34g2000yqm.googlegroups.com>
Content-Type: text/plain; charset="us-ascii"

Tiffany:
This clause,
>>>       where obhistory.obsid= (select obsid from update_cert b
                        where obhistory.obsid=b.obsid)
                and obhistory.hist_type= (select hist_type from update_cert b
                        where obhistory.obsid=b.obsid)
                and obhistory.hist_desc= (select hist_desc from update_cert b
                        where obhistory.obsid=b.obsid); ,

    does not constrain selection of updates to a row (tuple) that satifies all three equality conditions. If a row matches on each condition to any one or more rows in in the obhistory table, the condition succeeds.

Joe has posted the standard SQL solution: use of the existential quantifier EXISTS to test for all three conditions in one row. For more detail on existential (predicate logic) operators see the Existential Moments ... paper on Lex Jansen's site: http://www.lexjansen.com/cgi-bin/xsl_transform.php?x=tsql&s=sugi_t&c=sugi .
S


-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Tiffany
Sent: Wednesday, March 18, 2009 10:29 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: proc sql update


Hello everyone,
I am trying to update an Oracle table with new dates.  Each record has an expiration date that changes depending on certain events, so I want to update the end_date column in obhistory from the updated dates generated in the update_cert table.  Obhistory has many records.  The update should be done based on matched obsid, hist_type, and hist_desc.  The error I am getting is that the subquery generated more than 1 row.  I have tried this statement in a variety of ways, btu I can't seem to get the syntax correct.  Any suggestions?

these are two examples of what I have tried:

proc sql;
update nefop.obhistory
        set end_date = (select b.end_date from update_cert b, nefop.obhistory a
                where a.obsid=b.obsid and a.hist_type=b.hist_type and
a.hist_desc=b.hist_desc)
        where end_date in (select end_date from update_cert); quit;

proc sql;
update nefop.obhistory
        set end_date = (select end_date from update_cert b
                        where obhistory.obsid=b.obsid)
        where obhistory.obsid= (select obsid from update_cert b
                        where obhistory.obsid=b.obsid)
                and obhistory.hist_type= (select hist_type from update_cert b
                        where obhistory.obsid=b.obsid)
                and obhistory.hist_desc= (select hist_desc from update_cert b
                        where obhistory.obsid=b.obsid);
quit;

thanks so much!