Date:         Wed, 29 Jan 2003 16:45:40 -0500
Reply-To:     Yu Guo <Yu.Guo@VERISPAN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Yu Guo <Yu.Guo@VERISPAN.COM>
Subject:      Re: Updating a Table Using SQL
Comments: To: Kevin Myers <KevinMyers@AUSTIN.RR.COM>

        I found a similar example from Oracle's document. May be you want to
take a look:

        REQUIREMENT: -------------------------
        update myTable from myUpdates
           where myTable.myKey=myUpdates.myKey;

        My code (not
test):----------------------------------------------------------------
        UPDATE myTable  a
          SET (the columns names, such as col1, col2, col3) = (
SELECT[the column names]
                                        FROM myUpdates  b
                                        WHERE  a.myKey = b.myKey)
          WHERE  a.myKey IN
                    ( SELECT myKey
                FROM myUpdates d, myTable e
                WHERE d.myKey = e.myKey
                );


        -----------------------------------------------
        Complex Example from ORACLE doc:
        This example shows the following syntactic constructs of the UPDATE
statement:
        * Both forms of the set_clause together in a single statement
        * A correlated subquery
        * A where_clause to limit the updated rows

        UPDATE emp a
            SET deptno =
                (SELECT deptno
                    FROM dept
                    WHERE loc = 'BOSTON'),
                (sal, comm) =
                (SELECT 1.1*AVG(sal), 1.5*AVG(comm)
                  FROM emp b
                  WHERE a.deptno = b.deptno)
            WHERE deptno IN
                (SELECT deptno
                  FROM dept
                  WHERE loc = 'DALLAS'
                      OR loc = 'DETROIT');

        The above UPDATE statement performs the following operations:
        * Updates only those employees who work in Dallas or Detroit
        * Sets DEPTNO for these employees to the DEPTNO of Boston
        * Sets each employee's salary to 1.1 times the average salary of
their department
        * Sets each employee's commission to 1.5 times the average
commission of their department

        Raymond.




        From:   Kevin Myers <KevinMyers@AUSTIN.RR.COM> on 01/29/2003 04:15
PM
        Please respond to Kevin Myers
<KevinMyers@AUSTIN.RR.COM>@SMTP@ExchangePA
        To:     SAS-L@LISTSERV.UGA.EDU@SMTP@ExchangePA
        cc:

        Subject:        Updating a Table Using SQL

        Hello folks,

        I would like to update rows in one table with column values from the
corresponding rows of another table.  Conceptually, what I would like to do
is something like:

        update myTable from myUpdates
           where myTable.myKey=myUpdates.myKey;

        Only problem is that the above syntax or something similar doesn't
seem to exist.

        I could use a separate SET clause and corresponding query expression
for every individual column, but that would be very awkward, require lots of
code for lots of columns, and might produce very bad performance if the join
conditions in the query expressions for each individual column are evaluated
separately.  Yes, I could use a macro to generate the code, but that still
seems unnecessarily complex and wouldn't avoid any related performance
problems.

        Rather than using SQL, I could code the above as a DATA step using
MODIFY and BY statements.  However, I was hoping for an SQL solution in this
particular case.  Is there some (undocumented?) variation on the syntax of
the SQL UPDATE statement that provides this capability?

        Thanks,
        s/KAM