1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Multiple table update query

Discussion in 'SQL PL/SQL' started by nishapd, Jun 22, 2009.

  1. nishapd

    nishapd Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    TABLE1

    NAME
    RATE

    TABLE2

    NAME RATE1 DATE1
    NAME RATE2 DATE2
    NAME RATE3 DATE3

    Can anyone help me to write a query which can update RATE in TABLE1 based on
    following criteria ?

    We have same column 'NAME' in both the tables.

    First Find maximum Date from the TABLE2 and then take correspoinding rate from TABLE2 and update with the RATE column in TABLE1.
     
  2. krithika@2001

    krithika@2001 Active Member

    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    80
    Hi

    try this one

    Code (SQL):
    UPDATE table1
       SET rate = (SELECT rate
                     FROM table2
                    WHERE dt = (SELECT MAX (dt)
                                  FROM table2));
    regards
    Krithika
     
  3. Shekhar81

    Shekhar81 Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Right krithika but you forgot group by

    Code (SQL):
    UPDATE table1
       SET rate = (SELECT rate
                     FROM table2
                    WHERE date1 = (SELECT   MAX (date1)
                                       FROM table2
                                   GROUP BY name1))
     
  4. nishapd

    nishapd Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    This will update wrong number of row. So we need to mention somewhere about the common column in both the tables ...'NAME'
     
  5. nishapd

    nishapd Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    It is giving me an error that 'single-row subquery returns more than one row'
     
  6. krithika@2001

    krithika@2001 Active Member

    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    80
    There is no need to use group by.we have used = in subquery which should return only one row.but if we use group by it will return more than one row
    thats y ur getting an error.



    Regards
    Krithika
     
  7. nishapd

    nishapd Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    without using group by it is afftecting more rows than it should.
     
  8. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Errors in the above queries..

    1. Name is subquery is not referenced / compared to name in Main query, because of which subquery returns more rows.
    2. Exists clause is not meantioned because of which more rows will be updated
    3. Group by is not needed id Nmae field is referenced.

    Try the below query (Not Tested)

    Code (SQL):
    UPDATE table1 t1
       SET rate = (SELECT MAX(rate) --- Or SELECT rate if no duplicate rate for NAME+DT combination
                     FROM table2 t2
                    WHERE t2.name = t1.name
                      AND dt = (SELECT MAX (dt)
                                  FROM table2 t3
                                 WHERE t3.name = t2.name ))
    WHERE EXISTS (SELECT NULL
                    FROM table2 t2
                   WHERE t2.name = t1.name)
     
     
  9. krithika@2001

    krithika@2001 Active Member

    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    80
    Excellent.But i am getting the output without the exists clause.Can you explain me that logic




    Regards
    Krithika
     
  10. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    The query will work , without Exists clause also. But without Exists clause the query will update entire table table1 irrespective of Name's presence in table2. ie, Rate field will be updated to NULL even if the name is not present in Table2 , which might be not acceptable always. But it is not good practice either way.
     
  11. nishapd

    nishapd Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    I think the query is working fine for me....

    Now can you please help me where I need to add condition for following creteria ?

    1. update only those records where rate is null in TABLE1
    2. update only those records for those NAME, exists in TABLE2.
    3. update only those records for those date is not null in TABLE2.

    I appreciate your help.
     
  12. nishapd

    nishapd Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Can you please check my following query which i updated for those 2 condition as follow..

    Code (SQL):
    UPDATE table1 t1
       SET rate =
              (SELECT MAX
                         (rate)
                   --- Or SELECT rate if no duplicate rate for NAME+DT combination
                 FROM table2 t2
                WHERE t2.NAME = t1.NAME
                  AND dt IS NOT NULL
                  AND dt = (SELECT MAX (dt)
                              FROM table2 t3
                             WHERE t3.NAME = t2.NAME))
     WHERE EXISTS (SELECT NULL
                     FROM table2 t2
                    WHERE t2.NAME = t1.NAME) AND rate IS NULL
     
  13. nishapd

    nishapd Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Thanks to all who shared their knowledge with me.
     
  14. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Hope you got the answer for all your quries... .. .
     
  15. nishapd

    nishapd Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Sorry for the late reply. Thanks for asking.....Yes, I solved all my queries with the help of your query.

    Once again Thanks to all who helped me.