0

I am facing a strange issue which I have never faced before. I have a update query like below

Update A
Set A.Col10 = E.Col10, A.Col11 = E.Col11
FROM TableA A 
INNER JOIN TableB B on A.Col1 = B.Col1
INNER JOIN TableC C on C.Col1 = B.Col2
INNER JOIN TableD D on D.Col1 = C.Col2
INNER JOIN TableE E on E.Col1 = D.Col2
WHERE D.Col3 BETWEEN @StartDate AND @ENDDate
AND E.Col10 <> A.Col10

When i am running above statement it is giving message that 810 rows are updated. but when I am selecting based on the same criteria I am still getting those 810 rows.

SELECT A.Col10 , E.Col10
FROM TableA A 
INNER JOIN TableB B on A.Col1 = B.Col1
INNER JOIN TableC C on C.Col1 = B.Col2
INNER JOIN TableD D on D.Col1 = C.Col2
INNER JOIN TableE E on E.Col1 = D.Col2
WHERE D.Col3 BETWEEN @StartDate AND @ENDDate
AND E.Col10 <> A.Col10

no matter how many times I update the table it is still showing the same count. now, my question is where I am going wrong and what are those records which got updated when actually no records are updated.

  • Are any triggers on it? – Sabin Bio Jan 5 '16 at 6:51
  • @sabinbio No, none of these tables have trigger. – Zerotoinfinity Jan 5 '16 at 6:52
  • 1
    If the result of those joins is not unique, sql server will update A.Col10 with every E.Col10 value that matches your query, so at the end there might be the same amount of rows in the second query as in the first statement. – Michael Cherevko Jan 5 '16 at 7:01
  • 1
    For example - there are 81 A values and 11 E values, one A value match one E value, so sql server will update each A value 10 times = 810. The final result is the same(one A value match one E value), so when you select those values you still get 810 results. – Michael Cherevko Jan 5 '16 at 7:12
  • 1
    @MichaelCherevko OMG! That makes complete sense. How can I miss that... I need to have another condition in where clause to get matching rows (1-1). I am sure that's the problem.. let me test with the new query and I will post my outcome here. I request you to please post your comment as an answer. – Zerotoinfinity Jan 5 '16 at 7:57
2

If the result of those joins is not unique, sql server will update A.Col10 with every E.Col10 value that matches your query, so at the end there might be the same amount of rows in the second query as in the first statement
For example - there are 81 A values and 11 E values, one A value match one E value, so sql server will update each A value 10 times = 810. The final result is the same(one A value match one E value), so when you select those values you still get 810 results.

  • 1
    But if the values have been updated 1 or 10 time to A.Col10 = E.Col10 then AND E.Col10 <> A.Col10 should be false – paparazzo Jan 5 '16 at 15:59
  • 1
    Yeah, it is related to 1-M relationship among tables, so SQL Server is updating A->B then says that B should be A. I've now made uniqueness in my table and my problem is resolved. Thanks for your answer. – Zerotoinfinity Jan 6 '16 at 6:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.