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!

Update with Corelated Subquery

Discussion in 'SQL PL/SQL' started by orafan, Mar 17, 2009.

  1. orafan

    orafan Active Member

    Messages:
    23
    Likes Received:
    3
    Trophy Points:
    90
    Hi again

    I know that this can be simply done with a cursor and a procedure. But is there any way I can use a corelated subquery to update a column in a table. What i am saying is like

    I have two tables users_list and users_email. Users List has all the informations about users (more than 80 columns!). users_email has only userid and email. Email is blank in users_list. Now I want to update email of all users in users_list from email in users_email table.

    1. users_list
    (
    userid,
    username,
    Email,
    ----
    ----
    ----
    )

    2. users_email
    (
    userid,
    email
    )

    I can do this with a procedure where I pick userid and email from users_email in a cursor and update the users_list table, but how can i do this with plain sql. Something like
    Code (Text):

    update users_list
     set email = (select  email from users_email UE, users_list UL
                        where UE.userid = UL.userid)
     
    I know the above won't work but trying along those lines.

    Thanks
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    hmmm it might be possible with straight sql but i don't know how, but i wrote the procedure for you, in case u find that difficult too :)
    Code (Text):

    CREATE OR REPLACE PROCEDURE users_list_update
    AS
       CURSOR c1
       IS
          SELECT *
            FROM users_email;
    BEGIN
       FOR c1rec IN c1
       LOOP
          UPDATE users_list
             SET email = c1rec.email
           WHERE empid = c1rec.empid;
       END LOOP;
    END users_list_update;
     
    just compile this and execute and u r done!
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Always do Straight Sql whenever possible .

    Code (Text):

    update users_list ul
    set ul.email = (select  ue.email
                         from users_email ue
                         where ue.userid = ul.userid)
    Where Exists ( select  NULL
                         from users_email ue
                         where ue.userid = ul.userid)
    Then also u r done :)
     
  4. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    hmmm a very good way rajavu.. actually i wasn't able to figure out so gave the proc which seemed easier...
     
  5. orafan

    orafan Active Member

    Messages:
    23
    Likes Received:
    3
    Trophy Points:
    90
    thank u rajavu and tyro

    rajavu that was really good... i was trying along those lines, knew that i had to use exists clause but was not "getting it"!

    And tyro yeah, thanks for the procedure. Actually i have to get rid of this phobia of mine against procedures! :(