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.

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? :)

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.

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

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.

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