ORA - 1427 Correlated Query issue

[diane55]

Thanks ahead of time for your review!

I am on Oracle 9i trying to write an update query.
Below is the query that returns an error:
ORA-00913: to many values
When I remove the p.case_id I get the following error:
ORA-1427: single row query rturns more than one row.

What i am trying to accomplish with this statement is an update to the idnt table - specifically copying the contents of the Last_Name column into the Company_Name column when the ofic_cd is equal to P50 and the first_name and the company_name is null.

As you can see from the links I need data from two different tables, but I need to link through the third also to get there.
Does anybody have any ideas or suggestions on how I can write this query to mine out the data and make the update into the correct column?

Thanks for your time !!!!!!!

update CRDBA.idnt i
set i.company_name =
(select p.seq, p.case_id
from crdba.idnt i,
crdba.pty p,
crdba.case c
where c.ofic_cd = 'P50'
and i.first_name is null
and i.company_name is null
and i.idnt_id = p.idnt_id
and p.case_id = rc.case_id )
where exists
(select p.seq, p.case_id
from crdba.idnt i,
crdba.case c,
crdba.pty p
where c.ofic_cd = 'P50'
and i.first_name is null
and i.company_name is null
and i.idnt_id = p.idnt_id
and p.case_id = rc.case_id);

 

[SantaMufasa]

Diane,

I believe that we can help you better if you begin by simply narrating us through the business scenario for your update, without the preconceptions of your existing code. For example:

I want to update CRDBA.idnt's COMPANY_NAME column with the value from <source column name here> WHERE ...<finish narrative>

Don't try to use EXISTS or any other syntax in the narrative...simply assert the "business rules" that apply, along with references to table and column names where appropriate.


We'll then compose something based upon your narrative, once we understand your scenario.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ChrisHunt]

Something like this?

UPDATE update CRDBA.idnt i
SET    i.company_name = i.last_name
WHERE  i.first_name IS NULL
AND    i.company_name IS NULL
AND    i.idnt_id IN (SELECT p.idnt_id
                     FROM   crdba.pty p,
                            crdba.case c
                     WHERE  c.ofic_cd = 'P50'
                     AND    p.case_id = c.case_id )

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[diane55]

SantaMufasa -
I need to copy the contents of the crdba.idnt.Last_Name column into the crdba.idnt.Company_Name column when the crdba.case.ofic_cd is equal to P50 and the crdba.idnt.first_name and the crdba.idnt.company_name is null and crdba.idnt.idnt_id is equal to crdba.pty.idnt_id
and crdba.pty.case_id is equal to crdba.case.case_id

I hope this is what you have requested - thanks for your help.

 

[SantaMufasa]

Diane,

The code that Chris posted about 90 minutes prior to your excellent function narrative, appears to be equally excellent...His code appears to match your narrative perfectly.

Have you tried Chris's code? If so, how did it work for you? If it worked as prescribed, please be sure to award a

by clicking the link, [Thank ChrisHunt for this valuable post!].

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]