ORA-01427 [message #338125] |
Sun, 03 August 2008 21:44  |
bpiret
Messages: 1 Registered: August 2008
|
Junior Member |
|
|
Hi,
I have two tables. Both have identical column names and datatypes having 'id' as the primary key.
table_o has 7758 records.
table_r has 36468 records.
select count(*) from
(select id from table_o
minus
select id from table_r)
....yields 6708 ids.
My mission is to create table_z which will include table_r plus the 6708 records associated with the aforementioned IDs. The table will be defined with the exact same columns/datatypes as table_o and table_r, except that I need to add one additional column. For those 6708 records, I'll need to remove the value from one existing column and insert it into the new additional column.
As a first step, I tried:
CREATE TABLE table_z(id, a, b, c) AS
select * from table_o where id =
(select id from table_o
minus
select id from table_r)
...which resulted in an ORA-01427. By the way, this table_z definition above does not include the new additional column. It is the exact definition of table_o and table_r.
I would appreciate any help on how to proceed with this.
Thank you in advance for your time.
|
|
|
Re: ORA-01427 [message #338129 is a reply to message #338125] |
Sun, 03 August 2008 22:00   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
As stated in http://ora-01427.ora-code.com/:
Quote: | ORA-01427: single-row subquery returns more than one row
Cause: The outer query must use one of the keywords ANY, ALL, IN, or NOT IN to specify values to compare because the subquery returned more than one row.
Action: Use ANY, ALL, IN, or NOT IN to specify which values to compare or reword the query so only one row is retrieved.
| In this case, I would rather get rid of MINUS subquery and used NOT EXISTS from TABLE_R.
|
|
|
Re: ORA-01427 [message #338160 is a reply to message #338129] |
Mon, 04 August 2008 00:51  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | In this case, I would rather get rid of MINUS subquery and used NOT EXISTS from TABLE_R.
|
Or NOT IN depending on cardinality.
Beware of NULLs (if any).
Regards
Michel
|
|
|