Home » SQL & PL/SQL » SQL & PL/SQL » How to update a table with subquery returning more than one row
How to update a table with subquery returning more than one row [message #200801] Wed, 01 November 2006 07:04 Go to next message
sreehari
Messages: 101
Registered: May 2006
Senior Member
Hi all

How to update a table with sub query that is returning more than one row..


UPDATE COMPANY_LOD
SET (USER_ID,PHONE,FAX) = (SELECT POETIC_USER_LOD.USER_ID, PUL.PHONE, PUL.FAX
from PUL
where PUL.pub_id = 'DEFAULT_COMP_1' and company_lod.pub_id = 'COMP_1')


ora-01427-Single row subquery returning more than one row..

How can i change the above query to update the table..

Thanks
Re: How to update a table with subquery returning more than one row [message #200803 is a reply to message #200801] Wed, 01 November 2006 07:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If the problem is just that there are duplicate rows returned by the sub query, then you can add a DISTINCT to the subquery.

Otherwise, you can't do it.
Re: How to update a table with subquery returning more than one row [message #200806 is a reply to message #200801] Wed, 01 November 2006 07:18 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
It could still give the error if there is more than one distinct value. An alternative would be simply to take the first one if there is more than one option (although the choice may not necessarily be the "right" one):

UPDATE COMPANY_LOD
SET (USER_ID,PHONE,FAX) = (SELECT POETIC_USER_LOD.USER_ID, PUL.PHONE, PUL.FAX
from PUL
where PUL.pub_id = 'DEFAULT_COMP_1' and company_lod.pub_id = 'COMP_1'
and rownum=1)
Re: How to update a table with subquery returning more than one row [message #200809 is a reply to message #200801] Wed, 01 November 2006 07:29 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Sorry, ignore that. It's rubbish. It doesn't work. I think you may be misunderstanding the point of the update anyway. There should be some sort of correlation between COMPANY_LOD
and PUL for this to work correctly i.e. I would expect to see some sort of join:

UPDATE COMPANY_LOD
SET (USER_ID,PHONE,FAX) = (SELECT POETIC_USER_LOD.USER_ID, PUL.PHONE, PUL.FAX
from PUL
where PUL.pub_id = 'DEFAULT_COMP_1' and company_lod.pub_id = 'COMP_1'
and company_lod.xxxx = pul.yyyyy)


Re: How to update a table with subquery returning more than one row [message #200816 is a reply to message #200801] Wed, 01 November 2006 07:53 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
The reason I thought it wouldn't work was that I only tested it with one row and suddenly thought, "It will be using rownum from the main query rather than the subquery and only appears to work because I'm updating one row."

However, after setting up a more extensive test case, it does seem to work correctly:

drop table x;
	   
create table x (x number, y number, z number);
	   
insert into x values (56, 7, 9);
	   
INSERT INTO x values (54, 13, 99);

INSERT INTO x values (59, 11, 154);

drop table z;
	   
create table z (x number, y number, z number);
	   
insert into z values (56, 9, 13);
	   
insert into z values (56, 14, 25);
	   
insert into z values (54, 19, 22);
	   
insert into z values (59, 1, 0);
	   
insert into z values (59, 23, 4);
	
update x x set (y,z) = (select Z.y,Z.z from z where z.x = x.x and rownum=1); 
	   
SELECT * FROM X;


I still think the basic problem with sreehari's query is that there is no correlation between the main query and the subquery.
Re: How to update a table with subquery returning more than one row [message #200823 is a reply to message #200816] Wed, 01 November 2006 08:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It also doesn't get round the problem that it's basically picking a random row to use.
Re: How to update a table with subquery returning more than one row [message #200990 is a reply to message #200809] Thu, 02 November 2006 02:52 Go to previous message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
First of all, originally posted query wouldn't run at all (it wouldn't have a chance to get into TOO-MANY-ROWS) - so, either it is not correctly posted, or ... something else. I *guess* "poetic_user_lod" and "pul" are the same table, but the query doesn't say so (no formatting to use colors):

UPDATE COMPANY_LOD
SET (USER_ID,PHONE,FAX) =
(SELECT POETIC_USER_LOD.USER_ID, PUL.PHONE, PUL.FAX
from PUL --> where is POETIC_USER_LOD?
where PUL.pub_id = 'DEFAULT_COMP_1'
and company_lod.pub_id = 'COMP_1'
);

If those tables are the same, how about this slightly modified Cthulhu's solution: of course, find correlation columns between two tables and move part of the WHERE clause out of subquery?
UPDATE company_lod c SET
  (c.user_id, c.phone, c.fax) = 
  (SELECT p.user_id, p.phone, p.fax
   FROM poetic_user_lod p
   WHERE p.pub_id = 'DEFAULT_COMP_1'
     AND p.some_column = c.some_column  --> do you have anything like this?
  )
WHERE c.pub_id = 'COMP_1';              --> move that out from a subquery
Would that make sense, anyway?
Previous Topic: Validate a Field
Next Topic: Returning All Duplicate Rows
Goto Forum:
  


Current Time: Tue Dec 06 16:08:12 CST 2016

Total time taken to generate the page: 0.06742 seconds