SQL Server Performance

update inner join

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by fmardani, Jun 14, 2007.

  1. fmardani New Member

    Hi,
    As you can see the following two tables have different data.
    So the IDs will vary.
    Can you let me know the sql query to update table1
    Thanks
    -----------------
    Table1

    IndexIDNameParentIndexID
    1BR DATEDNULL
    2BR IPENULL
    3BR NYMEXNULL
    4DUBAINULL
    5F10MEDCCNULL
    6F10MEDFCNULL
    7F10NWECCNULL
    8F10NWEFBNULL
    9F10NWEFCNULL
    10xxNULL
    ...
    13BR(1,1)NULL
    ...
    20WTI NYMEXNull
    ...
    156zNull
    --------------------
    Table2

    table2IDtable2NameTable2ParentIndexID
    1BR DATED129
    2BR IPE56
    3BR NYMEX2
    4DUBAI129
    5F10MEDCC9
    6F10MEDFC9
    7F10NWECC9
    8F10NWEFB9
    9F10NWEFC13
    ...
    13F35NWEFB129
    ...
    56WTI NYMEXNULL
    ...
    129BR(1,1)131
    ...
    131xxNULL
    ------------------

    The idea is to come up with an update query to update table1
    to have this kind of data. Thanks

    IndexIDNameParentIndexID
    1BR DATED13
    2BR IPE20
    3BR NYMEX2
    4DUBAI13
    5F10MEDCC12
    6F10MEDFC12
    7F10NWECC12
    8F10NWEFB12
    12F10NWEFCNULL
    ...
    ...
    ...

    This is what i have so far:
    update
    Table1.ParentIndexID
    set
    Table1.ParentIndexID =
    from
    Table1
    inner join Table2 on Table1.IndexID = Table2.table2ParentIndexID
  2. Adriaan New Member

    Set up a SELECT query that shows the results from the column that will be updated, and from the column that contains the new value, so that it's showing you only the rows that need to be updated.

    For example:

    SELECT table1.col = table2.col
    FROM table1 INNER JOIN table2
    ON table1.key = table2.key

    Now replace only the SELECT part - starting with FROM, the rest remains the same!

    UPDATE table1 SET table1.col = table2.col
    FROM table1 INNER JOIN table2
    ON table1.key = table2.key

Share This Page