4 Replies Latest reply on Feb 11, 2011 12:18 AM by 837979

    Update statement with inner join

    654684
      Hello everyone. I am am trying to do an update statement with an inner join. I have found several examples of SQL statements that work with Sql server and mysql but they don't work in Oracle. Does anyone know the proper way in Oracle 10G? I am trying to update all fields in one table from fields in another table.

      for example:

      UPDATE table3
      SET
      TL3.name = TL2.name,
      TL3.status = TL2.status,
      TL3.date = TL2.date
      FROM table3 TL3 JOIN table2 TL2
      ON (TL3.unique_id = TL2.unique_id);

      any help will be appreciated.
        • 1. Re: Update statement with inner join
          riedelme
          debUVI wrote:
          Hello everyone. I am am trying to do an update statement with an inner join. I have found several examples of SQL statements that work with Sql server and mysql but they don't work in Oracle. Does anyone know the proper way in Oracle 10G? I am trying to update all fields in one table from fields in another table.

          for example:

          UPDATE table3
          SET
          TL3.name = TL2.name,
          TL3.status = TL2.status,
          TL3.date = TL2.date
          FROM table3 TL3 JOIN table2 TL2
          ON (TL3.unique_id = TL2.unique_id);

          any help will be appreciated.
          The SQL/PLSQL forum would be a better place for this quesiton but since it is here ...

          Use a correlated subquery in the SET clause, something like (untested)
          UPDATE table3 tl3
             SET (TL3.name ,TL3.status = TL2.status,TL2.date) =
                       (select tl2.name, tl2status, tl2.date
                          FROM table2 TL2 join table3 ON (TL3.unique_id = TL2.unique_id)
            where exists (
                     select 0
                        from table2
                      where table2.unique_id = tl3.unique_id
                     )
          Edited by: riedelme on Feb 10, 2011 7:20 AM
          • 2. Re: Update statement with inner join
            Frank Kulash
            Hi,

            You can also use MERGE, like this:
            MERGE INTO  table3     dst
            USING   (
                     SELECT  unique_id
                     ,         name
                     ,         status
                     ,         dt          -- DATE is not a good column name
                     FROM    table2
                 )          src
            ON     (dst.unique_id     = src.unique_id)
            WHEN MATCHED THEN UPDATE
            SET     dst.name     = src.name
            ,     dst.status     = src.status
            ,     dst.dt          = src.dt
            ;
            Unlike UPDATE, this lets you avoid essentially doing the same sub-query twice: once in the SET clause and then again in the WHERE clause.
            Like UPDATE, you don't acutally join the table being changed (table3 in this case) to the other table(s); that is, the FROM clause of the suib-query does not include table3.

            Riedelme is right; you'll get better response to SQL questions like this in the SQL and PL/SQL forum:
            PL/SQL and SQL
            • 3. Re: Update statement with inner join
              654684
              Thank you both very much. Both options worked. Sorry about the placement of the post. Thanks again.
              • 4. Re: Update statement with inner join
                837979
                This will also update the required rows.

                Update Table3
                Set (Name, Status, Date) =
                (     
                     Select Name, Status, Date
                     From Table2
                     Where Table2.Unique_ID = Table3.Unique_ID
                )
                Where Unique_ID In (Select Unique_ID From Table2)
                ;