Home » SQL & PL/SQL » SQL & PL/SQL » UPDATE query with three table join (3 merged) (oracle sql)
UPDATE query with three table join (3 merged) [message #682694] Tue, 10 November 2020 10:54 Go to next message
harshitawk
Messages: 1
Registered: November 2020
Junior Member
HI Team,

I need help in the below query :

Req: I HAVE THREE COLUMNS FROM THREE TABLES

TABLE3.FIRSTNAME TABLE3.LASTNAME TABLE1.ACCNAME
HAR WAL HAR WAL
ATT SSS ATT SSS

I CREATED BELOW UPDATE QUERY TO UPDATE TABLE AND I NEED TO UPDATE TABLE 1ACCNAME WITH FISRTNAME LASTNAME BUT BELOW QUERY THROWING ERROR :"Ora-01427 single-row subquery returns more than one row"

IM USING oRACLE SQL DEV



UPDATE BPD.TABLE1 SET BPD.TABLE1.ACCNAME = ( select BPD.TABLE3.FIRSTNAME || ' ' || BPD.TABLE3.LASTNAME from ((BPD.TABLE1 INNER JOIN BPD.TABLE2 ON BPD.TABLE2.ACCID = BCPD.TABLE1.ID)
INNER JOIN BPD.TABLE3 ON BCPD.TABLE2.CONTACTID =BPD.TABLE.ID ) WHERE BPD.TABLE1.ID IN ( select BCPD.TABLE1.ID from BCPD.TABLE1 where rownum >=1));
Re: UPDATE query with three table join [message #682695 is a reply to message #682694] Tue, 10 November 2020 12:15 Go to previous messageGo to next message
Michel Cadot
Messages: 67648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: UPDATE query with three table join [message #682701 is a reply to message #682694] Tue, 10 November 2020 22:38 Go to previous messageGo to next message
flyboy
Messages: 1898
Registered: November 2006
Senior Member
Hi,

Firstly: typing all text in uppercase is considered shouting and sign of poor netiquette. Please, minimize use of uppercase words. Even SQL code may be in lowercase as it is case insensitive.

Secondly: format the code, it is really hard to orientate in it.

Thirdly: your use of schema prefix BCPD is probably wrong, as it does not match table name and column reference. Also, for BPD.TABLE.ID, BPD.TABLE is not referenced in FROM clause. Logically, it should be TABLE3, but it is hard to tell without knowing underlying data.

After fixing this, you could run the SELECT subquery standalone and assure that it really returns multiple rows. Hence the error message - you cannot put more than one value to one column in one row.

It seems you want to UPDATE TABLE1 rows with values from other tables based on TABLE1.ID. If so, put that directly to the subquery expression, something like (note TABLE1 is nor referenced in subquery FROM clause):
update bpd.table1
set bpd.table1.accname = (
    select bpd.table3.firstname || ' ' || bpd.table3.lastname
    from bpd.table2 inner join bpd.table3 on bpd.table2.contactid = bpd.<appropriate table>.id
    where bpd.table2.accid = bpd.table1.id
)
-- use this WHERE if you want to update only rows with corresponding values from TABLE3
-- without this WHERE condition, rows without values in TABLE3 will be updated with NULL
--where exists( <subquery from the above SET clause> )
/
Of course, it may fail if there are multiple rows in TABLE3 for single TABLE1.ID If so, you shall reconsider the logic - which single value shall be used.

There are other methods mentioned e.g. in this article: https://oracle-base.com/articles/misc/updates-based-on-queries
You might find other ways for achieving this goal.
Re: UPDATE query with three table join [message #682704 is a reply to message #682694] Tue, 10 November 2020 23:57 Go to previous messageGo to next message
Michel Cadot
Messages: 67648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Instead of wasting time repeating the same question you should take it following the forum rules and providing what is requested:

Michel Cadot wrote on Tue, 10 November 2020 19:15

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

[Updated on: Wed, 18 November 2020 23:58]

Report message to a moderator

Re: UPDATE query with three table join (3 merged) [message #682783 is a reply to message #682694] Wed, 18 November 2020 16:07 Go to previous message
flyboy
Messages: 1898
Registered: November 2006
Senior Member
@harshitawk: As you were willing to post the question three times here, do you not have any feedback (other than re-posting the question again) to the answer I provided?

If no, I would suppose my response as wasting time and refrain from answering you any more. Thank you for understanding.
Previous Topic: Count of records with the max(date) and Max(lastrecordid)
Next Topic: Query with LIKE too SLOW...
Goto Forum:
  


Current Time: Thu Jan 21 17:46:27 CST 2021