SELECT AND UPDATE STATEMENT - Tibor Karaszi

How about using UPDATE with an OUTPUT clause (assuming 2005)?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

SELECT AND UPDATE STATEMENT - robie.boisver

Hi,

I want to have a stored procedure that basically perform a SELECT and
return a dataset to me application and update a fields for all records
returned by the previous SELECT.

Let me give you more information. I have a SQL Table which contains
somes fields. The most important fields for this question are ID,
Name, Featured, Featured_Count. Want I want to do is to retrieve the
featured item that are displayed the less. So the SELECT statement
is:

SELECT * FROM Items WHERE Featured = 1 ORDER BY Featured_Count ASC

From now, I want to mark them because the application displayed them.
So what I want to be able to do is:

UPDATE Items SET Featured_Count = (Featured_Count+1) WHERE (ID IN
[Previous SELECT]).

I have tried to update rows from subquery like :

UPDATE Items SET Featured_Count = (Featured_Count+1) WHERE (ID SELECT
ID FROM Items WHERE Featured = 1 ORDER BY Featured_Count ASC)  but
first I am not sure about performance and for any reason, sometime the
IDs returned by the subquery are not the same from the first SELECT
statement because it's a web application and many users are consulting
the page so the fields are updated regulary.

Any suggestion?

SELECT AND UPDATE STATEMENT - TheSQLGuru

Assuming the ID is an identity PK, put those into a temp table.

--you need this to keep others from accessing the same data while you
read/update
SET TRANSACTION ISOLATIONLEVEL SERIALIZEABLE

select id
into #tmp
FROM Items WHERE Featured = 1 ORDER BY Featured_Count ASC

--now output the data
SELECT * FROM Items i inner join #tmp t on t.id = i.id

--now do the update
update items set featured_count = featured_count + 1
where id in (select id from #tmp)

SET TRANSACTION ISOLATIONLEVEL REPEATABLE READ (or whatever your system's
default is)

I left out error handling and transaction control.  I leave those for the
inquisitive reader.  :-)

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

SELECT AND UPDATE STATEMENT - TheSQLGuru

Glad I could help.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net



Alright, I fixed the parameter problem. I just want to thank you
Kevin, you fixed my problem!!

SELECT AND UPDATE STATEMENT - TheSQLGuru

Performace no.  CONCURRENCY quite possibly (which could make it seem like
things are slower due to delays in other processes accessing entities with
extra locks on them due to SERIALIZABLE).  But as long as you have very
short transactions and you don't need hundreds+ hits per second on this
table there shouldn't be any issue.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net



BY the way, does this way of doing things has any performance impact ?

SELECT AND UPDATE STATEMENT - robie.boisver

I am using SQL 2000.

I forgot to tell that my query also contains a TOP 5.

On May 9, 2:00=A0am, "Tibor Karaszi"
ttp://sqlblog.com/blogs/tibor_karaszi

SELECT AND UPDATE STATEMENT - robie.boisver

e

It works thanks but cause another problem. Im passing 2 parameters to
my stored procedure and it seems that sp in not able to see them. I
got the following exception:

Error 137: Must declare variable @MaxCount

Any idea ??

SELECT AND UPDATE STATEMENT - robie.boisver

s
the


T




Alright, I fixed the parameter problem. I just want to thank you
Kevin, you fixed my problem!!

SELECT AND UPDATE STATEMENT - robie.boisver

s
the


T




BY the way, does this way of doing things has any performance impact ?