This discussion is archived
2 Replies Latest reply: May 30, 2012 6:24 AM by AlbertoFaenza RSS

Update SQL with multiple table joins

940571 Newbie
Currently Being Moderated
IN A TABLE I HAVE DATA AS

COLOUMN1     COLOUMN2 COLOUMN     COLOUMN4 .... .....          
A          1
B          2
C          3
D          4
E          5
F          6
G          7
H          8
I           9
And now I want to Update this Coloum2 based on the validation result from 5 diff tables.
e.g.

DESIRED RESULT IS:
Select Claose with diff validation will pick up a value & continue replacing from 'A' to 'I'
Say for e.g. where '1' replace with '11' where '2' replace with '22' etc. etc.

IF BY PL/SQL IT IS ABLE TO ACHIEVE .. HOW CAN IT BE??

I tried something tike this but it was of no help..

UPDATE TABLE1 A
SET A.COLOUMN1 = (SELECT C.COLOUMN1
FROM TABLE2 B,
TABLE3 C,
TABLE4 D,
TABLE5 E
WHERE A.COLOMUN2 = B.COLOUMN2
AND A.COLOUMN3 = C.COLOUMN3
AND A.COLOMUN4 = D.COLOUMN4
AND A.COLOUMN5 = E.COLOUMN5)

WHERE ??? ????
  • 1. Re: Update SQL with multiple table joins
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Welcome to the forum!

    When several tables are involved, MERGE is often simpler and more efficient than UPDATE.
    http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9016.htm#sthref6559

    Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved.
    Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
    Simplify the problem as much as possible. For example, if your real problem involves 5 tables, see if you can create a similar problem that only involves 2 or 3 tables, just to see how it's done. Explain that you really have 5 tables, and you'll get a solution that can easily be changed to work with 5 tables.
    If you're asking about a DML statement, such as UPDATE, the CREATE TABLE and INSERT statements should re-create the tables as they are before the DML, and the results will be the contents of the changed table(s) when everything is finished.
    Always say which version of Oracle you're using.
    See the forum FAQ {message:id=9360002}

    Edited by: Frank Kulash on May 30, 2012 9:04 AM
  • 2. Re: Update SQL with multiple table joins
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    first of all it should be nice if you would have posted some more information, like table creation and sample data.

    You can check this [url:https://forums.oracle.com/forums/thread.jspa?threadID=2174552#9360002]How do I ask a question on the forums?

    Anyway with a query like the one you have posted, without where clause, you will update column1 for all records in table A.
    Also in case the subquery
    SELECT C.COLOUMN1
    FROM TABLE2 B,
    TABLE3 C,
    TABLE4 D,
    TABLE5 E
    WHERE A.COLOMUN2 = B.COLOUMN2
    AND A.COLOUMN3 = C.COLOUMN3
    AND A.COLOMUN4 = D.COLOUMN4
    AND A.COLOUMN5 = E.COLOUMN5
    {code}
    cannot find any record it will return NULL and update the value of your column to NULL.
    
    As Frank suggested you can consider the merge option. If you still want to use update and you want to update only records matching the other tables you could also consider the following way using inline query:
    
    {code:sql}
    UPDATE (SELECT a.coloumn1 col1, c.coloumn1 col2
              FROM table1 a, table2 b, table3 c, table4 d
                 , table5 e
             WHERE     a.colomun2 = b.coloumn2
                   AND a.coloumn3 = c.coloumn3
                   AND a.colomun4 = d.coloumn4
                   AND a.coloumn5 = e.coloumn5)
       SET col1 = col2;
    {code}
    
    Note that without having idea about the structure of your tables and the amount of data I cannot tell you anything about performance.
    
    Regards.
    Al
    
    P.s.: The correct English word is COLUMN and not coloumn and CLAUSE and not claose :-)
    
    Edited by: Alberto Faenza on May 30, 2012 3:23 PM
    Added note about performance                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points