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!

Oracle Update:All Field values in Table1 where Field value found in Table2?

Discussion in 'SQL PL/SQL' started by rico, Jan 17, 2012.

  1. rico

    rico Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Is there a way to write this query without having to hardcode the names? I would need to run a query to find which names, count them, create an update statement for each, etc. I would have many more than 3, this is just an example ....

    Is there logic that would allow me to do this dynamically? Thanks ...

    BEGIN
    FOR i IN REVERSE 1..3 LOOP

    UPDATE sysadm.PS_MY_TABLE
    SET MY_FIELD = (SELECT a.emplid
    FROM serv.path@databasedblink a
    WHERE a.username = 'name1' and rownum = 1)
    WHERE MY_FIELD ='name1';

    UPDATE sysadm.PS_MY_TABLE
    SET MY_FIELD = (SELECT a.emplid
    FROM serv.path@databasedblink a
    WHERE a.username = 'name2' and rownum = 1)
    WHERE LSC_SAP_LOG_ADMN ='name2';

    UPDATE sysadm.PS_MY_TABLE
    SET MY_FIELD = (SELECT a.emplid
    FROM serv.path@databasedblink a
    WHERE a.username = 'name3' and rownum = 1)
    WHERE MY_FIELD ='name3';

    END LOOP;
    END;
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    You can Update them in single SQL as follows.

    Code (SQL):

    UPDATE sysadm.PS_MY_TABLE b
    SET MY_FIELD = (SELECT a.emplid
                              FROM serv.path@databasedblink a
                              WHERE a.username = b.MY_FIELD  AND rownum = 1)
    WHERE MY_FIELD IN ('name1','name2','name3')
    AND EXISTS (SELECT NULL
                       FROM serv.path@databasedblink a
                       WHERE a.username = b.MY_FIELD);
     
     
  3. rico

    rico Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Thanks Raj,

    That works great!
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What is your criteria for selecting these names? There is no example n your code showing what you did to arrive at these values. If we knew how you made that decision a dynamic example could possibly be provided to you.
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi,rico.
    If the number of parameters and their values isn't known in advance, and can will change according to the task, it is better to use dynamic slq/plsql as one operator maybe won't turn out.

    Example the first:

    Code (Text):

    declare
      v_steps int := 3;--count of names or etc...
      type t_list_name is table of varchar2(10);
      type t_list_emplid is table of number;
      v_list_name   t_list_name;
      v_list_emplid t_list_emplid;
    begin
      with step_1 as
       (select a.emplid,
               a.username,
               row_number() over(partition by a.username order by a.emplid) rn
          from serv.path@databasedblink a,
               (select /*+ no_unnest*/
                 concat('name',level) names
                from dual@databasedblink
                connect by level <= v_steps) b
         where a.username = b.names)
      select s1.emplid, s1.username bulk collect
        into v_list_emplid, v_list_name
        from step_1 s1;

      forall ind in 1 .. v_list_emplid.count
        update sysadm.ps_my_table
           set my_field = v_list_emplid(ind)
         where my_field = v_list_name(ind);
    end;
     
    Example two(we modify rajavu script):

    Code (Text):

    UPDATE sysadm.PS_MY_TABLE b
    SET MY_FIELD = (SELECT a.emplid
                              FROM serv.path@databasedblink a
                              WHERE a.username = b.MY_FIELD  AND rownum = 1)
    WHERE MY_FIELD IN (select
                         concat('name',level) names
                       from dual
                       connect by level <= 3)
    AND EXISTS (SELECT NULL
                       FROM serv.path@databasedblink a
                       WHERE a.username = b.MY_FIELD);

     
     
  6. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India

    Does this reply belongs to this thread?
     
  7. Hamza Bukhari

    Hamza Bukhari Active Member

    Messages:
    25
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Pakistan
    aaaaaaaaaaaaaaaaaaaaaaaaaa
    NoOp!! ;)
     
  8. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Please Try to post the things in their respective threads itself. Else it may lead others get confused.
     
    Hamza Bukhari likes this.