Update value from all rows update only once

Transact-SQL
1

i have the next update

    UPDATE a

SET Balance = Balance + (alt.Credits1-alt.Credits2)*Price
FROM ACCOUNTS a
INNER JOIN #LogTempTable alt
ON alt.AccountID = a.AccountID
WHERE
alt.Credits1<>alt.Credits2

if in #LogTempTable
there are 10 rows of accountid==1
i expect the value to change in the total of this 10 rows
but it changes only once why?

2

Maybe the credit values are the same?

What happens if you ran this?

Select *
FROM ACCOUNTS a
INNER JOIN #LogTempTable alt
ON alt.AccountID = a.AccountID
WHERE
alt.Credits1<>alt.Credits2
3

A given row in a table is only updated once by a single UPDATE statement. That's a relational rule to keep data correct.

UPDATE a
SET Balance = Balance + (alt.Credits1-alt.Credits2)*Price
FROM ACCOUNTS a
INNER JOIN (
    SELECT AccountID, SUM(Credits1) AS Credits1, SUM(Credits2) AS Credits2
    FROM #LogTempTable
    GROUP BY AccountID
) AS alt ON alt.AccountID = a.AccountID
WHERE
alt.Credits1<>alt.Credits2
1 Like
4

@ScottPletcher this is how i solved it as you did,
but can you explain why?
i was sure that every row is process and updated the table again and again

5

@ScottPletcher is also saying "a given row" and not "a given table"

6

Not really, no. A single UPDATE statement will only update any specific row one time. If multiple UPDATEs might occur, because multiple rows match on a JOIN, for example, which one row is actually used for the update can be considered to be random.

1 Like