Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01427 (Oracle 10.1.0.2.0)
ORA-01427 [message #338125] Sun, 03 August 2008 21:44 Go to next message
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: month values from date/varchar2 column (merged)
Next Topic: Slow querie with union all
Goto Forum:
  


Current Time: Wed Dec 07 18:53:04 CST 2016

Total time taken to generate the page: 0.07138 seconds