select and update in same query

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

General SQL Server Forums

New to SQL Server Programming

select and update in same query

Author

Topic

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-10-26 : 09:45:05

i am selecting the top 25 records from a table for processing. i would like to set the processstatus field to 3 as soon as i select the records.

is there a way to do this in one query?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-26 : 09:52:57

Select top 25 columns,3 as processstatus from table
order by somecol

Madhivanan

Failing to plan is Planning to fail

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-10-26 : 10:02:10

does that update the table or does it set the value to 3 in the select statement

i need to update the table for the records i am working with

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-26 : 11:02:52

quote:
Originally posted by tpiazza55

does that update the table or does it set the value to 3 in the select statement

i need to update the table for the records i am working with

Do you want to update the value only for top 25 rows?

Madhivanan

Failing to plan is Planning to fail

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-10-26 : 11:13:42

yes what i need is

select top 25 * from table update table set processstaus = 3 where id = @id -- the 25 record ids
for each record in 25
do something with the record
next

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-26 : 13:42:12

Update [Table]
Set processstatus = 3
From (Select Top 25 * from [Table]) proc inner join [Table] on proc.RecordID = [Table].recordID

Do you need to do something different with each record, and as you do it update the record? That could be done with a CURSOR loop where you "do something" to a record, update the processstatus to 3 and then move to the next record. Kind of depends on if your "do something" is a programmatic something.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-10-26 : 23:59:32

If you're using SQL Server 2005, the UPDATE statement now supports the OUTPUT clause, which does exactly what you want, something like this:

UPDATE TOP 25 [table]
SET processstatus = 3
OUTPUT INSERTED.col1, INSERTED.col2, INSERTED.processstatus

Kristen
Test

22859 Posts

Posted - 2007-10-27 : 04:05:15

" If you're using SQL Server 2005 ..."

Otherwise I reckon you will have to put the PKs for the top 25 rows into a temporary table, update them, and then SELECT them, with a JOIN. Need to lock the rows between Select and Update.

Kristen

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-10-28 : 18:27:43

Just update them in one statement then use them in another. That's what transactions are for - they disallow 'half completed' data manipulation so you'll be fine as long as you update and select the same rows consistently.

Kristen
Test

22859 Posts

Posted - 2007-10-29 : 05:04:16

"Just update them in one statement then use them in another."

That won't prevent new records being inserted by another user which could influence the records included in a TOP 25 - unless you take a Table Lock, which would be rather selfish!

I don't think there is a way to get a repeatable list, after an update, unless you first put the records you intend to update into a temporary table [or take a "Selfish Lock"

]

Kristen

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-10-29 : 17:26:18

Kristen - I come from an Oracle background and never quite remember how badly SQL Server sucks when it comes to multi-users & locking. You could use multi row versioning in SQL 2005 which would sort it out. The PK way you suggest probably is the best workaround.

Kristen
Test

22859 Posts

Posted - 2007-10-30 : 02:54:40

"never quite remember how badly SQL Server sucks when it comes to multi-users & locking"

Out of interest how would Oracle allow

SELECT TOP 25 ... ORDER BY SomeColumn

UPDATE TOP 25 ... ORDER BY SomeColumn

to ensure that the same 25 rows were updated?

Kristen

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-10-30 : 20:24:56

Oracle gives you read consistency at the time your transaction started by preserving versions of rows that are changing. When you read a row pending an update by another transaction it goes off to the version that was current at the time your transaction started and moves on. There is no waiting for the lock to be removed.

SQL Server's locking mechanism gives you read consistency at the time every other transaction is finished by making sure everything else is complete. Naturally this can cause performance issues because each contending transaction must be committed or rolled back before the result of your query can be determined. This is significantly compounded by introducing the concept of page and table locks, hence my 'sucks' comment.

So for this case, in Oracle I could actually do the update first knowing that because I am in a transaction, when I select them again I will always see the same ones no matter what other transactions have done to the database.

Hope this answers your question.

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-10-31 : 09:33:08

this will satisfy your desire

select top 25 column1,column2,column3='put the desired value to be set' from table_name

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-31 : 09:40:59

er... no it won't. that will just select the records and label your 'desired value' as column3

Em

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-01 : 02:16:31

quote:
Originally posted by arorarahul.0688

this will satisfy your desire

select top 25 column1,column2,column3='put the desired value to be set' from table_name

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

Well. Read my first reply

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-11-01 : 07:48:41

" Oracle gives you read consistency at the time your transaction started by preserving versions of rows that are changing"

Ah ... I remember reading about that now, thanks LoztInSpace

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-11-01 : 22:29:08

SQL Server 2005 now has the option to work like that too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources