SQLTeam.com | Weblogs | Forums

Update value from all rows update only once

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?

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

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

@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

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

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