Re: Updating Multiple Columns in Multiple Rows

From: <fitzjarrell_at_cox.net>
Date: Thu, 11 Oct 2007 06:09:55 -0700
Message-ID: <1192108195.169667.170050@50g2000hsm.googlegroups.com>

On Oct 10, 9:43 pm, DA Morgan <damor..._at_psoug.org> wrote:
> 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
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

You're making things too complicated. Notice the update statement that works is far simpler than any you've coded:

SQL> CREATE TABLE TEST (
2 ID NUMBER (10) NOT NULL
3 );

Table created.

SQL>
SQL>
SQL> CREATE TABLE T_OLD (

  2 ID NUMBER (10) NOT NULL
  3 ,ACC VARCHAR2(32)
  4 );

Table created.

SQL>
SQL>
SQL> CREATE TABLE T_NEW (

  2 ID NUMBER (10) NOT NULL
  3 ,ACC VARCHAR2(32)
  4 );

Table created.

SQL>
SQL>
SQL> INSERT INTO TEST (ID) SELECT 1 FROM dual;

1 row created.

SQL> INSERT INTO TEST (ID) SELECT 2 FROM dual;

1 row created.

SQL> INSERT INTO TEST (ID) SELECT 77 FROM dual;

1 row created.

SQL> INSERT INTO TEST (ID) SELECT 88 FROM dual;

1 row created.

SQL> --row that shouldn't get updated
SQL> INSERT INTO TEST (ID) SELECT 9999 FROM dual;

1 row created.

SQL>
SQL>
SQL> INSERT INTO T_OLD (ID, ACC) SELECT 1, 'AAAA' FROM dual;

1 row created.

SQL> INSERT INTO T_OLD (ID, ACC) SELECT 2, 'BBBB' FROM dual;

1 row created.

SQL>
SQL>
SQL> INSERT INTO T_NEW (ID, ACC) SELECT 77, 'AAAA' FROM dual;

1 row created.

SQL> INSERT INTO T_NEW (ID, ACC) SELECT 88, 'BBBB' FROM dual;

1 row created.

SQL>
SQL> SELECT * FROM TEST; ID

SQL> SELECT * FROM T_OLD; ID ACC

---------- --------------------------------
         1 AAAA
         2 BBBB

SQL> SELECT * FROM T_NEW; ID ACC

---------- --------------------------------
        77 AAAA
        88 BBBB

SQL>
SQL> -- The Error on the statement below is:
SQL> -- ORA-01427: single-row subquery returns more than one row
SQL>

SQL>
SQL> UPDATE TEST T1
2 SET ( ID ) = (

  3      SELECT T2.ID
  4      FROM TEST T2
  5      JOIN T_OLD ON T2.ID = T_OLD.ID
  6      JOIN T_NEW ON T_NEW.ACC = T_OLD.ACC
  7      WHERE T1.ID <> T2.ID
  8  ) WHERE T1.ID IN (
  9          SELECT T3.ID
 10          FROM TEST T3
 11          JOIN T_NEW ON T3.ID = T_NEW.ID
 12          JOIN T_OLD ON T_NEW.ACC = T_OLD.ACC
 13          WHERE T1.ID = T3.ID

14 );

SELECT T2.ID
*
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row

SQL>
SQL>
SQL> -- The error on this attempt is:
SQL> -- ORA-01407: cannot update ("PROSURV_0701"."TEST"."ID") to NULL
SQL> -- Even though I'm specifying records in 1 of the correlating

tables, it wants
SQL> -- to hit every row and set non-matches to null SQL> UPDATE TEST T1
  2 SET ( ID ) = (
  3 SELECT T_OLD.ID
  4 FROM TEST T2
  5 JOIN T_OLD ON T2.ID = T_OLD.ID
  6 JOIN T_NEW ON T_OLD.ACC = T_NEW.ACC   7 LEFT JOIN T_OLD T_OLD_2 ON T_OLD_2.ID = T2.ID   8 WHERE

  9          T2.ID <> T_OLD.ID
 10      AND T_OLD_2.ID IS NULL
 11      AND T1.ID = T2.ID

12 )
13 WHERE T1.ID IN (
14 SELECT ID FROM T_OLD
15 );
SET ( ID ) = (
*
ERROR at line 2:
ORA-01407: cannot update ("BING"."TEST"."ID") to NULL

SQL> -- This works
SQL>
SQL> update test t1
  2  set id = (select id from t_new
  3            where t_new.acc = (select acc from t_old where id =

t1.id))
4 where t1.id in (select id from t_old);

2 rows updated.

SQL>
SQL>
SQL> select * from test;

        ID
----------
        77
        88
        77
        88
      9999

SQL> David Fitzjarrell Received on Thu Oct 11 2007 - 08:09:55 CDT