Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2014
    Posts
    2

    Unanswered: update a column from a join

    Hi Oracle Gurus,

    Here is my select that I have to identify the child records that are Open (e.c7 < 6000) when the parent (t2068) c.c7 > 3


    SELECT c.c1000000161, c.c7, c.c1000000019, e.c1000000829 FROM t2068 c INNER JOIN t1533 e ON e.c1000000829 = c.c301572100 where c.c7 > 3 AND e.c7 < 6000


    What I want to do is update e.c7 = 6000 for the records identified from the statement above. How would I accomplish that?

  2. #2
    Join Date
    Sep 2014
    Posts
    2

    Update a column form a join

    Here is my select that I have to identify the child records that are Open (e.c7 < 6000) when the parent (t2068) c.c7 > 3


    SELECT c.c1000000161, c.c7, c.c1000000019, e.c1000000829 FROM t2068 c INNER JOIN t1533 e ON e.c1000000829 = c.c301572100 where c.c7 > 3 AND e.c7 < 6000


    What I want to do is update e.c7 = 6000 for the records identified from the statement above. How would I accomplish that?

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    update t1533 a
    set a.c7 = 6000
    where rowid in
    ( select e.rowid FROM t2068 c INNER JOIN t1533 e ON e.c1000000829 = 
    c.c301572100 where c.c7 > 3 AND e.c7 < 6000);
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    Code:
    UPDATE t1533 e
     SET   c7 = 6000
     WHERE c7 < 6000
      AND  EXISTS
           (SELECT 0
             FROM  t2068 c
             WHERE c.c301572100 = e.c1000000829
              AND  c.c7 > 3
           )
    ;

Posting Permissions

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