Results 1 to 3 of 3

Thread: Update Join

  1. #1
    Join Date
    Oct 2003
    Location
    UK
    Posts
    9

    Unanswered: Update Join

    Hi,

    I have two tables in a database to work with, and I want to use both but update a single field in only one of the DBs.

    I have a Select:

    select a.code
    a.desc
    b.keyval
    from table_1 a,
    table_2 b
    where a.desc = 'TEST'
    and left(b.keyval, length(a.code)) = a.code
    and b.classval = 'R'

    And say I wanted to update another field on table_2, on all records that would be pulled from the above Select.

    So the basic update statement would be:

    update table_2
    set foo = 'hello'

    How can I join the two?

    Hope I've explained that clearly... Any help would be appreciated, thanks.

  2. #2
    Join Date
    Jun 2003
    Posts
    34

    Re: Update Join

    Originally posted by sweevo
    Hi,

    I have two tables in a database to work with, and I want to use both but update a single field in only one of the DBs.

    I have a Select:

    select a.code
    a.desc
    b.keyval
    from table_1 a,
    table_2 b
    where a.desc = 'TEST'
    and left(b.keyval, length(a.code)) = a.code
    and b.classval = 'R'

    And say I wanted to update another field on table_2, on all records that would be pulled from the above Select.

    So the basic update statement would be:

    update table_2
    set foo = 'hello'

    How can I join the two?

    Hope I've explained that clearly... Any help would be appreciated, thanks.

    Can u be little more explainatory ? Give the table structures and the what u want to do in detail so that everyone could have a clear understanding

  3. #3
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Indeed we are missing some information here, but your statement will probably end up like the following :

    UPDATE TABLE_2 upd
    SET upd.FOO = (SELECT one_column_or_expression FROM TABLE_1 t1, TABLE_2 t2 WHERE <where_clause_subselect>)
    WHERE <where_clause_upd>

    (upd = alias for table_2, the one that needs to be updated

    The questions you should ask yourself are :
    1. What are the rows I want to update in TABLE_2. If you want them all to be updated, then you can omit the where clause <where_clause_upd>. Otherwise, you will have to restrict the number of rows with this where clause.

    2. Now that Oracle knows what rows in TABLE_2 need to be updated, you will have to tell it what value you want to assign to column FOO. That's where the subselect comes in. The subselect will be evaluated for each row matching <where_clause_upd>. Your subselect needs to return 1 row at the most. If your subselect returns more than 1 row, Oracle will raise an error. If your subselect returns 0 rows, a null value will be assigned to FOO. In the <where_clause_subselect>, you can refer to 3 "sources" of information : t1 (table_1) t2 (table_2 in select) and upd (table_2 that will be updated).

    Just give it a try...

Posting Permissions

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