Home » SQL & PL/SQL » SQL & PL/SQL » Update query - ora-01427 error (Oracle 9i)
Update query - ora-01427 error [message #351708] Thu, 02 October 2008 10:45 Go to next message
chrysalid
Messages: 4
Registered: October 2008
Junior Member
Hello,
I'm trying to run an update query, to replace all rows with a null date value, with a date from another table.
I get an ora-01427 error when I try to run the query.
I realize that the reason I get this is because there is >1 row being returned - but there are >1 records I need updated.
I'm thinking I need to use 'IN' instead of '=' but not sure how...
I've pasted my code below and attached a .pdf explanation (it's quite confusing).
Thanks in advance!!
Amber

UPDATE SILVICULTURE_ACTIVITY
SET SILA_START_DATE = (SELECT SILVICULTURE_AMENDMENT.SAMM_DIST_APPR_DATE
FROM
SILVICULTURE_ACTIVITY,
SILVICULTURE_COMPANY_ACTIVITY,
SILVICULTURE_AMENDMENT,
SILVICULTURE_PRESCRIPTION,
CUT_BLOCK
WHERE
SILVICULTURE_ACTIVITY.SICA_SEQ_NBR = SILVICULTURE_COMPANY_ACTIVITY.SICA_SEQ_NBR AND
SILVICULTURE_ACTIVITY.CUTB_SEQ_NBR = SILVICULTURE_PRESCRIPTION.CUTB_SEQ_NBR AND
SILVICULTURE_AMENDMENT.SILP_SEQ_NBR = SILVICULTURE_PRESCRIPTION.SILP_SEQ_NBR AND
SILVICULTURE_PRESCRIPTION.CUTB_SEQ_NBR = CUT_BLOCK.CUTB_SEQ_NBR AND
SICA_ACTIVITY_NAME = 'SP' AND
SILVICULTURE_AMENDMENT.SAMM_DIST_APPR_DATE IS NOT NULL)
WHERE
SILA_STATUS = 'P' AND SILA_START_DATE IS NULL AND
/
  • Attachment: SQL_exp.pdf
    (Size: 46.19KB, Downloaded 637 times)
Re: Update query - ora-01427 error [message #351713 is a reply to message #351708] Thu, 02 October 2008 12:52 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You are correct in your assumption, but the root of the problem is Oracle does not know which record in the lookup query to use to update your record. How would you know which one to use? You need to define a one to one relationship or use an aggregate function.

[added] and your update statement is invalid.

[Updated on: Thu, 02 October 2008 12:53]

Report message to a moderator

Re: Update query - ora-01427 error [message #351717 is a reply to message #351708] Thu, 02 October 2008 14:00 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@chrysalid,
chrysalid wrote on Thu, 02 October 2008 21:15

I'm thinking I need to use 'IN' instead of '=' but not sure how...


Replacing '=' with 'IN' also won't work. I thought its a wrong syntax in an UPDATE statement. Confused
CREATE TABLE tab_1
(
  Col_1 VARCHAR2(10)
);

INSERT INTO tab_1 VALUES ('A');
INSERT INTO tab_1 VALUES ('B');

SELECT * from tab_1;

Col_1
----------
A
B

CREATE TABLE tab_2
(
  Col_2 VARCHAR2(10)
);

INSERT INTO tab_2 VALUES ('C');
INSERT INTO tab_2 VALUES ('D');
INSERT INTO tab_2 VALUES ('E');
INSERT INTO tab_2 VALUES ('F');

SELECT * from tab_2;

Col_2
----------
C
D
E
F

Now,
UPDATE tab_1
SET col_1 = (SELECT col_2 FROM tab_2)
where col_1 = 'A';

ORA-01427: single-row-subquery returns more than one row


UPDATE tab_1
SET col_1 IN (SELECT col_2 FROM tab_2)
where col_1 = 'A';

ORA-00927: missing equal sign


chrysalid wrote on Thu, 02 October 2008 21:15

SILA_STATUS = 'P' AND SILA_START_DATE IS NULL AND
/


Guess your update statement is incomplete.

Anyways can you check if CUTB_SEQ_NBR in your PRESCRIPTION table(Foreign Key in that table) is unique? (I doubt it to be unique though). Same thing will go for SLIP_SEQ_NBR in your AMENDMENT table. If they are not unique, many to many relationship (bad relationship. Might end up in Cartesian Product) occurs through the joins you have given from all the way from ACTIVITY table to AMENDMENT table.

Also note that if the AMANDMENT table (source for your update table ACTIVITY) has a "many-end" of the relationship you will get the same error unless you restrict the number of records to one(by using aggregate functions might help).

Hope this helps.

Finally, Please read OraFAQ Forum Guide before posting.

Regards,
Jo

[Didn't see @joydivision's reply]



[Updated on: Thu, 02 October 2008 14:05]

Report message to a moderator

Re: Update query - ora-01427 error [message #352446 is a reply to message #351717] Tue, 07 October 2008 13:16 Go to previous messageGo to next message
chrysalid
Messages: 4
Registered: October 2008
Junior Member
Okay, I took a few days off and am now back to trying to sort this out.

One thing I realized is that I'm missing one more criteria - if I add:

AND SILVICULTURE_AMENDMENT.SAMM_AMENDMENT_NBR = '0'

to my SQL, I will only ever return a single SAMM_DIST_APPR_DATE for each SILA_START_DATE.

Does this make sense?

Now I have a 1 to 1 relationship, but my inside sub-query is still pulling multiple records.

Not sure how I make this work.

And regarding the suggestion to read the Forum Guide - what did I do wrong?

Thanks for your help!

Amber
Re: Update query - ora-01427 error [message #352454 is a reply to message #352446] Tue, 07 October 2008 15:34 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
chrysalid wrote on Tue, 07 October 2008 14:16
if I add:

AND SILVICULTURE_AMENDMENT.SAMM_AMENDMENT_NBR = '0'

to my SQL, I will only ever return a single SAMM_DIST_APPR_DATE for each SILA_START_DATE.



If you are still getting the error then you are incorrect by saying this.

Your understanding of the process I believe is incorrect. It's your join condition that needs to be one to one, not a one to one relationship of the column to be updated.

[Updated on: Tue, 07 October 2008 15:36]

Report message to a moderator

Re: Update query - ora-01427 error [message #352455 is a reply to message #352454] Tue, 07 October 2008 15:53 Go to previous messageGo to next message
chrysalid
Messages: 4
Registered: October 2008
Junior Member
Yes - I am aware that I don't know how to do it - that is why I'm posting here.
What I'm trying to make clear is that there IS a 1-to-1 relationship between the two fields, explained in my select query.
What I don't understand, is how to take that select statement, and make an update statement.
I tried to put something together on my own - and have had no luck.
I came here to find some help, not to have it pointed out to me that it's wrong - without ever getting suggestions on how to correct it...
Re: Update query - ora-01427 error [message #352479 is a reply to message #351708] Wed, 08 October 2008 00:31 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

One qry ( may not be directly linked to the problem)

Why are you directly joining SILVICULTURE_ACTIVITY with SILVICULTURE_COMPANY_ACTIVITY ? It may give multiple records . You can use WHERE EXISTS instead .

One more thing noticed is ,

NONE of the colums of SILVICULTURE_ACTIVITY of main query is reference in the subquery . That is the main reason for duplication .

Please revise your update statement .

Thumbs Up
Rajuvan.

[Updated on: Wed, 08 October 2008 00:32]

Report message to a moderator

Re: Update query - ora-01427 error [message #352619 is a reply to message #352479] Wed, 08 October 2008 11:00 Go to previous messageGo to next message
chrysalid
Messages: 4
Registered: October 2008
Junior Member
SILVICULTURE_ACTIVITY and SILVICULTURE_COMPANY_ACTIVITY are linked by SICA_SEQ_NBR and are necessary as part of the criteria (SILVICULTURE_COMPANY_ACTIVITY.SICA_ACTIVITY_NAME='SP')

For every SILVICULTURE_ACTIVITY record, there is exactly ONE SILVICULTURE_COMPANY_ACTIVITY record (I doubled-checked).

I assumed I needed SILVICULTURE_ACTIVITY in the subquery to use in the WHERE statement (to link to both SILVICULTURE_COMPANY_ACTIVITY and SILVICULTURE_PRESCRIPTION.

Maybe I dont'??

Thanks for your help Smile
Re: Update query - ora-01427 error [message #352847 is a reply to message #351708] Thu, 09 October 2008 22:16 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

revised your statement ? and still getting error ?

Thumbs Up
Rajuvan.
Previous Topic: Union - How to populate null field
Next Topic: Multiple joins through multiple tables
Goto Forum:
  


Current Time: Tue Dec 06 12:34:06 CST 2016

Total time taken to generate the page: 0.15164 seconds