SQL Server Performance Forum – Threads Archive

Updating composite Key through Query.

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

---

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

---

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?

---

]]>