UPDATE with embedded SELECT...(kind of)

[cw26]

Hi,

I'm trying to run and UPDATE in SQL PLUS. Basically, I have 2 tables - TABLE1 and TABLE2. TABLE1 contains an ID field that links it to TABLE2. TABLE1 has been altered to include a new field called NAME. There are no values for NAME yet in TABLE1 as these need to be taken from TABLE2 NAME where TABLE1 ID = TABLE2 ID.

So I need to loop through a ResultSet of sorts returned from TABLE2 of NAMEs where the IDs equal each other and UPDATE the NAME field in TABLE1 with the value from TABLE2.

I keep getting the error:
ORA-01427: single-row subquery returns more than one row

when I try something like this:

UPDATE TABLE1
SET NAME = (SELECT TABLE2.NAME FROM TABLE1, TABLE2
WHERE TABLE1.ID = TABLE2.ID);

Any suggestions would be appreciated. Thanks.

 

[BJCooperIT]

This error is telling you that there is more than one row in table2 that is being returned in your sub-query. Is the name in each matching table2 row the same for each ID? If your answer is "yes" then try:

UPDATE TABLE1 t1
SET t1.NAME = (SELECT t2.NAME
               FROM   TABLE2 t2
               WHERE  t2.ID = t1.ID
               AND    rownum = 1);

There is no need to requery table1 in your sub-query.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
^{When posting code, please use TGML to help readability. Thanks!}

 

[cw26]

Thanks for the reply,

There will be multiple names returned - a different name (possibly for each ID from TABLE2). So, that seems to be my problem. I need to run an update for all the rows in TABLE1 but the query from TABLE2 will return multiple rows of data.

TABLE2 has all the IDs and NAMES while TABLE has only some of the IDs and needs the corresponding NAMES where the IDs matchup.

Will this still work??

Thanks again.

 

[BJCooperIT]

If this is the structure:[tt]
table1 ID 123
table2 ID 123 Name: Bill[/tt]
or[tt]
table1 ID 123
table2 (no matching entry)[/tt]
then you have no problem.

However, if this is the structure:[tt]
table1 ID: 123
table2 ID: 123 Name: Bill
ID: 123 Name: Fred
ID: 123 Name: Tom[/tt]
Which name would you use to update table1?

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
^{When posting code, please use TGML to help readability. Thanks!}

 

[cw26]

Thanks again for the reply,

The structure is that of the first one you indicated. The NAMEs will always be one-to-one based on ID.

Will the example you provide using ROWNUM = 1 do the trick and go through row-by-row for each ID that matches up?? Is that all that I'm missing?

 

[BJCooperIT]

The rownum in the WHERE clause insures that only one row is returned by the sub-query. If you only have the first structure then the error was from the sub-query including table1 again improperly. All you should need is:

UPDATE TABLE1 t1
SET t1.NAME = (SELECT t2.NAME
               FROM   TABLE2 t2
               WHERE  t2.ID = t1.ID);

Good Luck!


^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
^{When posting code, please use TGML to help readability. Thanks!}

 

[cw26]

I tried your solution for above - now I get an error indicating that I cannot update the TABLE1.NAME to NULL.

Here's a example of the structure:

TABLE1
ID 121, NAME null
ID 123, NAME null

TABLE2
ID 121, NAME "Bob"
ID 122, NAME "Mike"
ID 123, NAME "Scott"

So basically I need to run an UPDATE on TABLE1 to popluate the NAMES based on ID....

Thank again!

 

[martincab]

What ora-error do you get?

Martin Cabrera
Oracle DBA/Programmer

 

[cw26]

ORA-01407: cannot update TABLE1.NAME to NULL

 

[cw26]

Also, I've verified that there are values for NAME for all IDs from TABLE2 that matchup to those IDs in TABLE1.

So there should be no NULL values coming from TABLE2 as it appears that the error is telling me...

 

[BJCooperIT]

OK...didn't anticipate that one:

SQL> create table TABLE1
  2   (ID       varchar2(5),
  3    NAME     varchar2(15));
SQL> insert into TABLE1 values('AAA','');
SQL> insert into TABLE1 values('BBB','');
SQL> 
SQL> create table TABLE2
  2   (ID       varchar2(5),
  3    NAME     varchar2(15));
SQL> 
SQL> insert into TABLE2 values('AAA','FRED');
SQL> 
SQL> UPDATE TABLE1 t1
  2  SET t1.NAME = (SELECT t2.NAME
  3                 FROM   TABLE2 t2
  4                 WHERE  t2.ID = t1.ID
  5                 AND    t2.NAME is not null);
SQL> 
SQL> select * from table1;

ID    NAME
----- ---------------
AAA   FRED
BBB

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
^{When posting code, please use TGML to help readability. Thanks!}

 

[cw26]

using AND T2.NAME IS NOT NULL still returns the same error message

??

 

[BJCooperIT]

Can you describe table1 and post it here?

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
^{When posting code, please use TGML to help readability. Thanks!}

 

[cw26]

TABLE1
ID NOT NULL VARCHAR(11)
NAME NOT NULL, VARCHAR(50)

Same fields defn's in TABLE2.

Of course there's more fields in both tables but these are the only two I'm dealing with.

TABLE2 does not have any NULL values for NAME.

 

[Turkbear]

Hi,
Try this variant of your original attempt:

UPDATE TABLE1
SET NAME = (SELECT MAX(TABLE2.NAME)from TABLE2 
WHERE TABLE1.ID = TABLE2.ID);

The MAX ( could be MIN ) should insure that 1 and only 1 name is returned for each ID found)

 

[BJCooperIT]

Since t1.NAME is not null that changes it a bit. I am puzzled why you are getting the error if all rows in table2 have name valued. There probably is a more sophisticated way to do this, but this worked for me:

SQL> create table TABLE1
  2   (ID       varchar2(5),
  3    NAME     varchar2(15) not null);
SQL> insert into TABLE1 values('AAA','JIM');
SQL> insert into TABLE1 values('BBB','BILL');
SQL> 
SQL> create table TABLE2
  2   (ID       varchar2(5),
  3    NAME     varchar2(15));
SQL> 
SQL> insert into TABLE2 values('AAA','FRED');
SQL> 
SQL> UPDATE TABLE1 t1
  2  SET t1.NAME = (SELECT t2.NAME
  3                 FROM   TABLE2 t2
  4                 WHERE  t2.ID = t1.ID)
  5  WHERE 0 !=    (SELECT count(*)
  6                 FROM   TABLE2 t2
  7                 WHERE  t2.ID = t1.ID
  8                 AND    t2.NAME is not null);
SQL> 
SQL> select * from table1;

ID    NAME
----- ---------------
AAA   FRED
BBB   BILL

Let me know if this works.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
^{When posting code, please use TGML to help readability. Thanks!}

 

[cw26]

Thanks BJCooperIT!!!

That worked. Not quite sure how just yet, but it worked.

It seems strange that I would have been getting a NULL error from TABLE2 that doesn't have any NULL values. I've double-checked that. Perhaps it was the NULLs on TABLE1 that were interferring with something....even though that's the table I was trying to update??

Anyways, thanks very much for your help!

 

[BJCooperIT]

I would suspect it might have to do with a table1 row that had no matching row in table2. Anyway, I am glad your problem is solved.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
^{When posting code, please use TGML to help readability. Thanks!}

 

[Tonnes]

You where not alone with this problem !
Thank you for the help with the extra where-statement.
It also helped me.
The problem seems to be that rows in table 2 that you dont have in table 1 are being processed with a NULL-update even though you have the where-statement (t2_id=t1_id).
Thats why you get an ORA-01407 if the column is NOT_NULL-defined.
I removed NOT_NULL in the name-column and the update worked without the extra where-statement.... but all names that wasnt in the where-statement (t2_id=t1_id) where NULL:ed even though they wasn't to be updated, and that I cant understand why.
Is it a bug ?