Select & Update @ same time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select & Update @ same time

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-08-18 : 14:38:07

Is there a way to select a set of data and also update the table it is selected from at the same time?

For instance, let's say I have MyTable as

UniqueID (int)
value(float)
timestamp(datetime)

Now let's say I want to do this

SELECT * From MyTable where timestamp > '08/12/03'

and

UPDATE MyTable Set TimeStamp = GETDATE() WHERE timestamp > '8/12/03'

But I want the update to occur when I select it, in case trx's are added to the table in between the Select & the Update.

Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-18 : 14:57:42

You could store the UniqueID values in a temporary table, then update MyTable based on the rows in the temp table.

SELECT UniqueID
INTO #Temp
FROM MyTable
WHERE timestamp > '08/12/03'

UPDATE MyTable
FROM MyTable m
INNER JOIN #Temp t ON m.UniqueID = t.UniqueID
SET m.timeStamp = GETDATE()
WHERE m.timestamp > '08/12/03'

Tara

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-08-18 : 15:05:48

You don't really want them done at the same time, I suspect. What you want is to avoid contention issues so that you're guaranteed the rows SELECTed are exactly the rows you UPDATEed.

You solve this by altering the transaction isolation level, or by supplying locking hints.

Jonathan
{0}

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-08-18 : 15:22:26

Thanks Tara. Already thought of that. As a matter of fact I could just update first with getdate() and then do the select WHERE date = datechangedto. But that's not my question.

Jonathon - You are correct - but I don't know what you are talking about for an answer, even after I checked out the help for isolation levels and locking hints - a bit above my head.

Thanks anyway, but it still appears as if my answer is to update first, then do the select.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-08-18 : 16:11:57

Ken-
Both the transaction isolation level and the locking hints do the same thing - place the appropriate lock on the table so that other users don't update your rows.

So one structure for handling this looks something like:

begin transaction
select *
from mytable (updlock) -- place update lock on rows and hold them
where timestamp > '2003-12-08'

update mytable
set ...
commit transaction

Jonathan
{0}

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-08-18 : 16:44:00

OK, I understand your fancy words! (ACID) :) However, the UPDATE WHERE condition would be the same as the SELECT. So, if a transaction was added to the table between the SELECT and the UPDATE wouldn't it be UPDATEd but not selected?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources