Results 1 to 3 of 3

Thread: Oracle UPDATE Joins

  1. #1
    Join Date
    Jul 2004
    Posts
    17

    Oracle UPDATE Joins

    MS ACCESS and I think SQL Server makes this very easy for me, but Oracle does not. I'm looking to merge two tables that have identical keys. Essentially, I want to expand column-wise, not row-wise.

    This is how it does work in Oracle
    UPDATE MAIN m
    SET col1 =
    (
    SELECT s.col1
    FROM SIBLING s
    WHERE m.key1 = s.key1
    AND m.key2 = s.key2
    AND m.key3 = s.key3
    ),
    col2 =
    (
    SELECT s.col2
    FROM SIBLING s
    WHERE m.key1 = s.key1
    AND m.key2 = s.key2
    AND m.key3 = s.key3
    );

    But, I think it's silly that I have to code the query twice to do this. Perhaps processing-wise, Oracle will be efficient about it, and only run it once.

    In MSFT, I get to do a join of the two tables, and then an update of one table, all in one shot.

    Is there a way to streamline this in Oracle any better than I have?

    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Yes Oracle does not allow FROM clause in an UPDATE statement however it allows to have multiple columns in set clause as

    UPDATE MAIN m
    SET (col1,col2) =
    (
    SELECT s.col1,s.col2
    FROM SIBLING s
    WHERE m.key1 = s.key1
    AND m.key2 = s.key2
    AND m.key3 = s.key3
    );

  3. #3
    Join Date
    Jul 2004
    Posts
    17
    That's great, just what I was looking for!

Posting Permissions

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