January 21, 2021 at 12:35 am
I'm doing an update using a self-join, and just want to confirm something. If the query updates a certain row I'll call row A, and then updates another row B based on a value in row A, does it look at the old value in row A, or the new value?
Since we can't do an order by in an update statement, there would be no way to know what order the rows are updated in, so I assume it would always look at the old value. Is there maybe an official Microsoft page explaining this?
January 21, 2021 at 3:53 pm
The old value. The new value can't be seen by a transaction modifying that table until the transaction's been committed.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 21, 2021 at 5:32 pm
Makes sense- thanks.
January 21, 2021 at 7:34 pm
OK, technically, other queries in the same transaction that did the UPDATE could see it before committing it, in something like this:
BEGIN TRANS
UPDATE
SELECT
COMMIT TRANS
But not within the context of a single UPDATE.
So, as you suspected, the answer is still the same: the UPDATE query can only see the old values.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 26, 2021 at 2:35 am
Here is an illustration that may help. Note that both columns don't wind up with the original value of V2 until the update ends (commits).
declare @table table (V1 int, V2 int)
insert into @table values (1,2), (3,4), (5,6)
select * from @table
update @table
set V1 = V2
, V2 = V1
select * from @Table
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 26, 2021 at 2:41 am
BUT, within the context of the same transaction you see the changes prior to the commit.
(Used a temp table here because table variables, like all variables, retain their current values despite ROLLBACK.)
create table #table (V1 int, V2 int)
insert into #table values (1,2), (3,4), (5,6)
select * from #table
begin transaction
update #table
set V1 = V2
, V2 = V1
select * from #Table -- changes visible within context of open transaction
rollback -- changes do not get committed
select * from #Table
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply