Re: Updating Multiple Columns in Multiple Rows

kc wrote:
> /*
> I'm having trouble updating multiple non-nullable columns in multiple
> rows in a single table, using 2 different tables to get the
> correlating data, in a single statement. Tables and 2 of the
> statements I've attempted are below:
>
> I want to update the 77 and 88 rows with the values of 1 and 2,
> linking them
> via the ACC value (AAAA and BBBB) in the T_OLD and T_NEW tables.
>
> The problem is determining which row gets which new value
>
> */
>
> -- Here are the tables
>
> CREATE TABLE TEST (
> ID NUMBER (10) NOT NULL
> );
>
> CREATE TABLE T_OLD (
> ID NUMBER (10) NOT NULL
> ,ACC VARCHAR2(32)
> );
>
> CREATE TABLE T_NEW (
> ID NUMBER (10) NOT NULL
> ,ACC VARCHAR2(32)
> );
>
> INSERT INTO TEST (ID) SELECT 1 FROM dual;
> INSERT INTO TEST (ID) SELECT 2 FROM dual;
> INSERT INTO TEST (ID) SELECT 77 FROM dual;
> INSERT INTO TEST (ID) SELECT 88 FROM dual;
> INSERT INTO TEST (ID) SELECT 9999 FROM dual; --row that shouldn't get
> updated
>
> INSERT INTO T_OLD (ID, ACC) SELECT 1, 'AAAA' FROM dual;
> INSERT INTO T_OLD (ID, ACC) SELECT 2, 'BBBB' FROM dual;
>
> INSERT INTO T_NEW (ID, ACC) SELECT 77, 'AAAA' FROM dual;
> INSERT INTO T_NEW (ID, ACC) SELECT 88, 'BBBB' FROM dual;
> /*
> SELECT * FROM TEST;
> SELECT * FROM T_OLD;
> SELECT * FROM T_NEW;
> */
> -- The Error on the statement below is:
> -- ORA-01427: single-row subquery returns more than one row
>
> UPDATE TEST T1
> SET ( ID ) = (
> SELECT T2.ID
> FROM TEST T2
> JOIN T_OLD ON T2.ID = T_OLD.ID
> JOIN T_NEW ON T_NEW.ACC = T_OLD.ACC
> WHERE T1.ID <> T2.ID
> ) WHERE T1.ID IN (
> SELECT T3.ID
> FROM TEST T3
> JOIN T_NEW ON T3.ID = T_NEW.ID
> JOIN T_OLD ON T_NEW.ACC = T_OLD.ACC
> WHERE T1.ID = T3.ID
> )
>
> -- The error on this attempt is:
> -- ORA-01407: cannot update ("PROSURV_0701"."TEST"."ID") to NULL
> -- Even though I'm specifying records in 1 of the correlating tables,
> it wants
> -- to hit every row and set non-matches to null
> UPDATE TEST T1
> SET ( ID ) = (
> SELECT T_OLD.ID
> FROM TEST T2
> JOIN T_OLD ON T2.ID = T_OLD.ID
> JOIN T_NEW ON T_OLD.ACC = T_NEW.ACC
> LEFT JOIN T_OLD T_OLD_2 ON T_OLD_2.ID = T2.ID
> WHERE
> T2.ID <> T_OLD.ID
> AND T_OLD_2.ID IS NULL
> AND T1.ID = T2.ID
> )
> WHERE T1.ID IN (
> SELECT ID FROM T_OLD
> );

Create a single result set from querying the two tables. Wrap it in parentheses as an inline view. Then use it to update your table.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org