Updating Multiple Columns in Multiple Rows

From: kc <kecanniff_at_hotmail.com>
Date: Wed, 10 Oct 2007 16:00:03 -0700
Message-ID: <1192057203.823262.170100@r29g2000hsg.googlegroups.com>

/*
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 );

Received on Wed Oct 10 2007 - 18:00:03 CDT