SQL Server Performance

Updating composite Key through Query.

Discussion in 'T-SQL Performance Tuning for Developers' started by gangulyarindam, Jan 13, 2005.

  1. gangulyarindam New Member

    I have two tables having three Common Columns : A, B and C. Table I is having Composite Key -> A + B + C and Table II is having Composite Key -> A + B.
    I wanted to update Column say C of Table I from the value of C of Table II on the basis of searching the value of A and B in Table II. I have issued the following SQL query :

    Update I
    Set I.C = III.C
    FROM I, (SELECT II.A, II.B, II.C FROM II WHERE II.A = 'VAL1' AND II.B = 'VAL2') III

    But the problem is that while executing it is not getting stopped and chuks out huge Memory and CPU resources making the other operations impossible.


    Arindam Ganguly
  2. Madhivanan Moderator

    Try this


    Update I
    Set T1.C = T2.C
    FROM I T1, II T2 WHERE T2.A = 'VAL1' AND T2.B = 'VAL2' and T1.A = T2.A AND T1.B =T2.B

    Madhivanan
  3. mmarovic Active Member

    Hi gangulyarindam,

    your query is missing join condition, you actually made cross join. To avoid such errors in future better use ansi join syntax.

    Are your composite keys primary keys?

Share This Page