Insert from two different tables (oracle)

[fo2sh]

Hello,

I have the below case.

CREATE TABLE "PRBT"."TM_TABLE_1" 
   (    "NID" NUMBER, 
    "STRNAME" VARCHAR2(240 BYTE) NOT NULL ENABLE, 
    "NORDERCODE" NUMBER NOT NULL ENABLE, 
    "NSTATEID" NUMBER DEFAULT 1 NOT NULL ENABLE, 
    "STRDETAILS" VARCHAR2(256 BYTE) DEFAULT NULL, 
    "STRAUTHOR" VARCHAR2(128 BYTE) NOT NULL ENABLE, 
    "NPICTUREID" NUMBER DEFAULT NULL, 
    "STRLATINNAME" VARCHAR2(512 BYTE), 
    "STRLATINAUTHOR" VARCHAR2(512 BYTE));

    CREATE TABLE "PRBT"."TM_TABLE_2" 
   (    "NID" NUMBER, 
    "NUSERID" NUMBER NOT NULL ENABLE, 
    "NITEMID" NUMBER, 
    "NCOST" NUMBER DEFAULT 0 NOT NULL ENABLE, 
    "DTCREATED" TIMESTAMP (6) DEFAULT SYSTIMESTAMP NOT NULL ENABLE, 
    "DTEXPIRE" TIMESTAMP (6), 
    "NTIMELIMIT" NUMBER, 
    "LISDELETED" NUMBER DEFAULT 0 NOT NULL ENABLE, 
    "LLOOPBACK" NUMBER DEFAULT NULL, 
    "LISGIFT" NUMBER DEFAULT 0 NOT NULL ENABLE, 
    "NTRIALPERIOD" NUMBER, 
    "NTRANSACTIONID" NUMBER, 
    "LISSYSTEM" NUMBER DEFAULT 0 NOT NULL ENABLE, 
    "NORDERCODE" NUMBER
   )

So, I want to insert into TM_TABLE_2.NITEMID the value of TM_TABLE_1.NID where TM_TABLE_2.NORDERCODE = TM_TABLE_1.NORDERCODE, keeping in mind that its possible to have more records inside TM_TABLE_1 than TM_TABLE_2 so I just want to insert NID's from TM_TABLE_1 into TM_TABLE for row's with NORDERCODE existed in both tables.

Any advise please!

Thanks in advance.

[RudyM]

Would this work for you? I tried in in MSSQL:

UPDATE TM_TABLE_2 
SET NITEMID = (SELECT T1.NID 
                FROM TM_TABLE_1 T1 
                WHERE T1.NORDERCODE = TM_TABLE_2.NORDERCODE )

[fo2sh]

Hello RudyM,

Thank you, i am getting the below error when trying such query.

Error report -
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"

Any ideas? :)

[RudyM]

That's what you meant by one table having more rows than the other, I just thought in terms of row count. You may have to add more conditions to this, perhaps even modify to:

UPDATE TM_TABLE_2 
SET NITEMID = (SELECT DISTINCT(T1.NID)
                FROM TM_TABLE_1 T1 
                WHERE T1.NORDERCODE = TM_TABLE_2.NORDERCODE )

Where it would simply look for a distinct NID in TM_TABLE_1.

[RudyM]

Also try this:

UPDATE TM_TABLE_2 T2
SET T2.NITEMID = (SELECT DISTINCT(T1.NID)
                    FROM TM_TABLE_1 T1 
                    WHERE T1.NORDERCODE = T2.NORDERCODE )

I saw you said Oracle, and hopefully I remember correctly that you can alias the update tables.

[fo2sh]

well, still same error

Error report -
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"

[fo2sh]

BTW, each row is holding different NORDERCODE value inside TM_TABLE_2 and in the same time each row from TM_TABLE_2 should have NITEM value from one row inside TM_TABLE_1.NID

Below is sample data from TM_TABLE_2

NUSERID NITEMID NCOST DTCREATION DTEXPIRE NTIMELIMIT LISDELETED LLOOPBACK LISGIFT NTRIALPERIOD NTRANSACTIONID LISSYSTEM NORDERCODE
1 10 2011-10-30 11:44:11 2592000 0 0 0 7555
1 1 2011-11-03 15:05:59 2592000 0 0 0 2044
1 1 2013-01-15 09:46:36 2592000 0 0 0 9996
1 1 2012-06-27 19:09:22 2592000 0 0 0 4822
1 1 2012-06-27 19:09:22 2592000 0 0 0 4823
1 1 2012-06-27 19:09:22 2592000 0 0 0 4824
1 1 2012-06-27 19:09:22 2592000 0 0 0 4825
1 1 2012-06-27 19:09:22 2592000 0 0 0 4826
1 1 2012-06-27 19:09:22 2592000 0 0 0 4827
1 1 2012-06-27 19:09:22 2592000 0 0 0 4828
1 1 2012-06-27 19:09:22 2592000 0 0 0 4829
1 1 2012-06-27 19:09:22 2592000 0 0 0 4830
1 1 2012-06-27 19:09:22 2592000 0 0 0 4831

[RudyM]

That means you need to add more conditions to the TM_TABLE_1 select query. Try selecting for a single NORDERCODE, see the possible values in the columns. Unless there are duplicates, but surely there's something that you can find to add in there to limit the rows returned.

[RudyM]

The error is explaining that the subquery from TM_TABLE_1 has multiple entries with same NORDERCODE. Select some data from there.