11 Replies Latest reply on Dec 9, 2009 3:33 AM by 304304

    Update query with Joins

    512918
      How to write a update query with joins?

      something like this
      UPDATE TBL_BASE_TO_PEOPLE2
      SET TBL_BASE_TO_PEOPLE2.BASE_CODE = TBL_BASES.BASE_CODE
      FROM TBL_BASE_TO_PEOPLE2, TBL_BASES
      WHERE SUBSTR(TBL_BASE_TO_PEOPLE2.BASE_CODE, 1, 6) = SUBSTR(TBL_BASES.BASE_CODE, 1, 6)

      Hope this kind of query works in other databases like mysql, sybase. What is the equivalent syntax for the above?

      I tried to use this query. But the subquery returns more than 1 row. Cant i update the above requirement in a single query. Help me.
        • 1. Re: Update query with Joins
          495663
          See this thread:

          update statements from 2 diff tables

          Peter D.
          • 2. Re: Update query with Joins
            516988
            GOOD QUESTION!

            I am still looking for the solution. I started the thread Peter D. points you to, but still I haven't figure it out. If you find out something PLEASE post it
            • 3. Re: Update query with Joins
              APC
              Providing SUBSTR(TBL_BASES.BASE_CODE, 1, 6) is unique, this ought to work for you...
              UPDATE TBL_BASE_TO_PEOPLE2 tbtp2
              SET BASE_CODE = ( SELECT BASE_CODE
                                FROM TBL_BASES tb
                                WHERE SUBSTR(tbtp2.BASE_CODE, 1, 6) = SUBSTR(tb.BASE_CODE, 1, 6) )
              /
              Cheers, APC

              Fixed layout to prevent widescreen format

              Message was edited by:
              APC
              • 4. Re: Update query with Joins
                SomeoneElse
                Oracle has the ability to update a table used in a join, however there is a restriction that Oracle must know in advance that the table acting as the source must be unique for each row in the target. (I hope I worded that correctly). You have the same restriction logically with a subquery because if it returns more than one row the update statement will fail. Here's an example:
                create table source_table
                (source_id    number primary key
                ,s_col1       number
                ,s_col2       number
                ,s_col3       number
                );

                create table target_table
                (target_id    number primary key
                ,t_col1       number
                ,t_col2       number
                ,t_col3       number
                );

                insert into source_table(source_id,s_col1,s_col2,s_col3) values (101,111,1.01,-1);
                insert into source_table(source_id,s_col1,s_col2,s_col3) values (102,222,2.02,-2);
                insert into source_table(source_id,s_col1,s_col2,s_col3) values (103,333,3.03,-3);
                insert into source_table(source_id,s_col1,s_col2,s_col3) values (104,444,4.04,-4);
                insert into source_table(source_id,s_col1,s_col2,s_col3) values (105,555,5.05,-5);

                insert into target_table(target_id,t_col1,t_col2,t_col3) values (101,null,null,null);
                insert into target_table(target_id,t_col1,t_col2,t_col3) values (102,null,null,null);
                insert into target_table(target_id,t_col1,t_col2,t_col3) values (103,null,null,null);
                insert into target_table(target_id,t_col1,t_col2,t_col3) values (104,null,null,null);
                insert into target_table(target_id,t_col1,t_col2,t_col3) values (105,null,null,null);
                commit;

                SQL> select * from target_table;

                           TARGET_ID               T_COL1               T_COL2               T_COL3
                -------------------- -------------------- -------------------- --------------------
                                 101
                                 102
                                 103
                                 104
                                 105

                update
                (
                   select t_col1,t_col2,t_col3,s_col1,s_col2,s_col3
                   from   target_table t
                         ,source_table s
                   where  t.target_id = s.source_id
                )
                set t_col1 = s_col1
                   ,t_col2 = s_col2
                   ,t_col3 = s_col3
                ;

                5 rows updated.

                SQL> select * from target_table;

                           TARGET_ID               T_COL1               T_COL2               T_COL3
                -------------------- -------------------- -------------------- --------------------
                                 101                  111                 1.01                   -1
                                 102                  222                 2.02                   -2
                                 103                  333                 3.03                   -3
                                 104                  444                 4.04                   -4
                                 105                  555                 5.05                   -5
                • 5. Re: Update query with Joins
                  516988
                  Hallelujah Brother!!!!

                  This is also what i was looking for. I am posting a new thread about deleting with join tables, can you please take a look. thanks!!!!!
                  • 6. Re: Update query with Joins
                    516988
                    Eric,
                    i still have my problem with join table update. I am sending you some data example in case you can help:

                    I have table x with the following data:
                    ACTNO     BRNNO     PRDNO     ACT_CD     ACCOUNT_NUM
                    100     103     4     'no account_cd'     1001034
                    100     104     3     'no account_cd'     1001043
                    100     108     4     'no account_cd'     1001084
                    100     109     3     'no account_cd'     1001093
                    100     110     3     'no account_cd'     1001103

                    and table y with the following data:

                    ACTNO     BRNNO     PRDNO     ACTCD     ACCOUNT_NUM
                    100     104     3     0     1001043
                    100     108     4     9     1001084
                    100     109     3     8     1001093
                    100     110     3     0     1001103
                    100     112     3     0     1001123


                    I want to update x.act_cd with y.actcd where x.account_num=y.account_num.

                    How can I do that?
                    • 7. Re: Update query with Joins
                      SomeoneElse
                      i still have my problem with join table update.
                      And the problem is.....?
                      • 8. Re: Update query with Joins
                        245482
                        Theodora,

                        Here's a quick survey of your options.

                        1. Scalar sub-query
                        UPDATE x
                           SET foo = (
                               SELECT y.foo
                                 FROM y
                                WHERE y.bar = x.bar);
                        1b. Scalar sub-query with existential predicate
                        UPDATE x
                           SET foo = (
                               SELECT y.foo
                                 FROM y
                                WHERE y.bar = x.bar)
                        WHERE EXISTS (
                               SELECT y.foo
                                 FROM y
                                WHERE y.bar = x.bar);
                        2. Updatable view (requires FK from x to y)
                        UPDATE (
                        SELECT x.foo AS old_foo,
                               y.foo AS new_foo
                          FROM x, y
                        WHERE y.bar = x.bar)
                           SET old_foo = new_foo;
                        3. Merge
                        MERGE INTO x
                        USING (SELECT foo, bar
                                  FROM y ) y
                            ON (y.bar = x.bar)
                        WHEN matched THEN
                        UPDATE
                           SET x.foo = y.foo
                        WHEN not_matched THEN
                        NULL;
                        • 9. Re: Update query with Joins
                          Ariean
                          SomeoneElse wrote:
                          Oracle has the ability to update a table used in a join, however there is a restriction that Oracle must know in advance that the table acting as the source must be unique for each row in the target. (I hope I worded that correctly). You have the same restriction logically with a subquery because if it returns more than one row the update statement will fail. Here's an example:
                          create table source_table
                          (source_id    number primary key
                          ,s_col1       number
                          ,s_col2       number
                          ,s_col3       number
                          );

                          create table target_table
                          (target_id    number primary key
                          ,t_col1       number
                          ,t_col2       number
                          ,t_col3       number
                          );

                          insert into source_table(source_id,s_col1,s_col2,s_col3) values (101,111,1.01,-1);
                          insert into source_table(source_id,s_col1,s_col2,s_col3) values (102,222,2.02,-2);
                          insert into source_table(source_id,s_col1,s_col2,s_col3) values (103,333,3.03,-3);
                          insert into source_table(source_id,s_col1,s_col2,s_col3) values (104,444,4.04,-4);
                          insert into source_table(source_id,s_col1,s_col2,s_col3) values (105,555,5.05,-5);

                          insert into target_table(target_id,t_col1,t_col2,t_col3) values (101,null,null,null);
                          insert into target_table(target_id,t_col1,t_col2,t_col3) values (102,null,null,null);
                          insert into target_table(target_id,t_col1,t_col2,t_col3) values (103,null,null,null);
                          insert into target_table(target_id,t_col1,t_col2,t_col3) values (104,null,null,null);
                          insert into target_table(target_id,t_col1,t_col2,t_col3) values (105,null,null,null);
                          commit;

                          SQL> select * from target_table;

                          TARGET_ID               T_COL1               T_COL2               T_COL3
                          -------------------- -------------------- -------------------- --------------------
                          101
                          102
                          103
                          104
                          105

                          update
                          (
                          select t_col1,t_col2,t_col3,s_col1,s_col2,s_col3
                          from   target_table t
                          ,source_table s
                          where  t.target_id = s.source_id
                          )
                          set t_col1 = s_col1
                          ,t_col2 = s_col2
                          ,t_col3 = s_col3
                          ;

                          5 rows updated.

                          SQL> select * from target_table;

                          TARGET_ID               T_COL1               T_COL2               T_COL3
                          -------------------- -------------------- -------------------- --------------------
                          101                  111                 1.01                   -1
                          102                  222                 2.02                   -2
                          103                  333                 3.03                   -3
                          104                  444                 4.04                   -4
                          105                  555                 5.05                   -5
                          Hi,

                          I read your thread posted here and trying to customize my query as per my needs, but i was unsuccessful since the column names are same in my case for both the tables, could you please help me here, Thank you.

                          UPDATE
                          (
                          SELECT ORG.FCST_REVENUE_MON_AMT, FCST.FCST_REVENUE_MON_AMT
                          FROM
                          WC_ELT_OM ORG, WC_ELT_OM_SNAPSHOT_FCST FCST
                          WHERE
                          FCST.PRODUCT_TYPE_CODE=ORG.PRODUCT_TYPE_CODE AND
                          FCST.REGION_WID=ORG.REGION_WID AND
                          FCST.SNAPSHOT_DATE_WID='20091130' AND
                          ORG.SNAPSHOT_DATE_WID='20091201'
                          )SET ORG.FCST_REVENUE_MON_AMT= FCST.FCST_REVENUE_MON_AMT
                          • 10. Re: Update query with Joins
                            304304
                            Try using aliases for the column names that are identical (i.e. old_... and new_... or something that makes sense). That worked beautifullly for me.
                            • 11. Re: Update query with Joins
                              304304
                              Try this.

                              UPDATE
                              (
                              SELECT
                              ORG.FCST_REVENUE_MON_AMT as ORG_FCST_REVENUE_MON_AMT,
                              FCST.FCST_REVENUE_MON_AMT as FCST_FCST_REVENUE_MON_AMT
                              FROM
                              WC_ELT_OM ORG, WC_ELT_OM_SNAPSHOT_FCST FCST
                              WHERE
                              FCST.PRODUCT_TYPE_CODE=ORG.PRODUCT_TYPE_CODE AND
                              FCST.REGION_WID=ORG.REGION_WID AND
                              FCST.SNAPSHOT_DATE_WID='20091130' AND
                              ORG.SNAPSHOT_DATE_WID='20091201'
                              )
                              SET ORG_FCST_REVENUE_MON_AMT = FCST_FCST_REVENUE_MON_AMT