This discussion is archived
11 Replies Latest reply: Dec 8, 2009 7:33 PM by 304304 RSS

Update query with Joins

512918 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    See this thread:

    update statements from 2 diff tables

    Peter D.
  • 2. Re: Update query with Joins
    516988 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    i still have my problem with join table update.
    And the problem is.....?
  • 8. Re: Update query with Joins
    245482 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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