Update with predicate
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Update with predicate

  1. #1
    Join Date
    Dec 2001
    Posts
    141

    Update with predicate

    Hi all !
    I would like to update a table with a particular select like the following :

    select c1,c2,c3 from tab1;
    c1 c2 c3
    -- -- --
    1 10 100
    2 10 100
    3 10 100

    select c1,c2,c3 from tab2;
    c1 c2 c3
    -- -- --
    10 100
    10 100
    10 100
    1 20 200

    I would like to update c1 column in tab2 with the value of tab1 to obtain :

    select c1,c2,c3 from tab2;
    c1 c2 c3
    -- -- --
    1 10 100
    2 10 100
    3 10 100
    1 20 200

    Is there a simple way to do that with one update ?
    Thanks a lot in advance !

  2. #2
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    Not sure how you would do it in one SQL for all the records but if you dont mind to putting this in PL/SQL loop, the following SQL might help.

    /
    Code:
    update tab2
    set c1 = ( select c1 from tab1 where c1 not in ( select c1 from tab2  where c1 is not null)
    and rownum = 1)
    where c1 is null
    and rownum = 1
    -- Dilip

  3. #3
    Join Date
    Dec 2001
    Posts
    141
    Thanks for the reply !
    I'm so sorry ... I've forgot to say that c2 and c3 columns are the primary key in both tables.
    So I have to join the two tables on these key ...
    Any idea ?

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Then you need a better example, because c2+c3 can't be the PKs given your data.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  5. #5
    Join Date
    Dec 2001
    Posts
    141
    Sorry ...

    select c1,c2,c3 from tab1;
    c1 c2 c3
    -- -- --
    1 117 A
    2 185 D
    3 169 Z

    select c1,c2,c3 from tab2;
    c1 c2 c3
    -- -- --
    117 A
    185 D
    169 Z
    1 220 Q

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Code:
    UPDATE
       T2
    SET
       C1 =
          (
          SELECT
             C1
          FROM
             T1
          WHERE
             T1.C2 = T2.C2 AND
             T1.C3 = T2.C3
          )
    WHERE
       EXISTS
          (
          SELECT
             1
          FROM
             T1
          WHERE
             T1.C2 = T2.C2 AND
             T1.C3 = T2.C3
          )
    You may be able to directly update an in-line view as well. I'll leave that to someone else to show. I hate Oracle's implementation of that so bad that I never use it.

    Welcome to Oracle

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  7. #7
    Join Date
    Dec 2001
    Posts
    141
    Thanks a lot Chris !
    It's exactly what I need ... I miss the "where exists" part !
    Best regards !

  8. #8
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    You can also try this.

    Code:
     update tab2 set c1 = (select c1 from tab1 where c2 = tab2.c2 and c3 = tab2.c3) 
     where c1 is null;
    -- Dilip

  9. #9
    Join Date
    Dec 2001
    Posts
    141
    Thanks a lot too ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width