select and update at same time

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 2005 Forums

Transact-SQL (2005)

select and update at same time

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-05-25 : 06:36:02

Hi, I want to make a sp wher I select a record, and at the same time update the number of views for it.

What I've got is this, however it is not working:

ALTER PROCEDURE [dbo].[GetLastCreated]
@Type int
AS

DECLARE @CNA_ID Int

SELECT top(1)
[CNA_ID],
[Type],
[Title],
[Summary],
[Views],
[Status],
[Created],
[Lastmodified]
FROM
[dbo].[Campaign]
Where
[Type] = @Type
and
[Status] = 1
order by
[Created] desc

SET @CNA_ID = SCOPE_IDENTITY()

UPDATE
dbo.Campaign
SET
Views = Views + 1
WHERE
CNA_ID = @CNA_ID

Sachin.Nand

2937 Posts

Posted - 2010-05-25 : 07:42:00

[code]
DECLARE @CNA_ID Int

SET @CNA_ID =(SELECT top(1) [CNA_ID] FROM [dbo].[Campaign]Where[Type] = @Type and [Status] = 1 order by
[Created] desc)

UPDATE
dbo.Campaign
SET
Views = Views + 1
WHERE
CNA_ID = @CNA_ID
[/code]

PBUH

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-05-25 : 07:46:22

ok, but will it still return my record?

The secret to creativity is knowing how to hide your sources. (Einstein)

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-05-25 : 07:48:07

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Sachin.Nand

2937 Posts

Posted - 2010-05-25 : 07:53:02

[code]
ALTER PROCEDURE [dbo].[GetLastCreated]
@Type int
AS

DECLARE @CNA_ID Int

SET @CNA_ID =(SELECT top(1) [CNA_ID] FROM [dbo].[Campaign]Where[Type] = @Type and [Status] = 1 order by
[Created] desc)

SELECT top(1)
[CNA_ID],
[Type],
[Title],
[Summary],
[Views],
[Status],
[Created],
[Lastmodified]
FROM
[dbo].[Campaign]
Where
[Type] = @Type
and
[Status] = 1
order by
[Created] desc

UPDATE
dbo.Campaign
SET
Views = Views + 1
WHERE
CNA_ID = @CNA_ID

[/code]

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-05-25 : 08:00:54

Or maybe the update statement can be written much better way.


UPDATE
dbo.Campaign
SET
Views = Views + 1
from
(
SELECT top(1)[CNA_ID] FROM [dbo].[Campaign]Where[Type] = @Type and [Status] = 1 order by
[Created] desc
)T where T.[CNA_ID]=Campaign.[CNA_ID]

PBUH

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-05-25 : 08:03:51

Yes, but that will not return the record...

The secret to creativity is knowing how to hide your sources. (Einstein)

Sachin.Nand

2937 Posts

Posted - 2010-05-25 : 08:06:18

Yes it wont you have to add the select statement and then use the update statement.

PBUH

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-05-25 : 08:11:37

Ok, but then ypu need to do 2 selects...
1 select to identify the record
1 update to update the record
1 select for returning the record

I would like to do
1 select to identify the record
1 update
then return the selected record WITHOUT a second select

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-05-25 : 08:30:58

If you are using 2005 or above, this should work:


UPDATE
dbo.Campaign
SET
Views = Views + 1
OUTPUT
	 inserted.[CNA_ID]
	,inserted.[Type]
	,inserted.[Title]
	,inserted.[Summary]
	,inserted.[Views]
	,inserted.[Status]
	,inserted.[Created]
	,inserted.[Lastmodified]
from
(
SELECT top 1 [CNA_ID] FROM [dbo].[Campaign]Where[Type] = @Type and [Status] = 1 order by
[Created] desc
)T where T.[CNA_ID]=Campaign.[CNA_ID]

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-05-25 : 08:43:09

Yes, that seems correct, thanks!!!

The secret to creativity is knowing how to hide your sources. (Einstein)

Sachin.Nand

2937 Posts

Posted - 2010-05-25 : 08:48:09

quote:
Originally posted by trouble2

Yes, that seems correct, thanks!!!

The secret to creativity is knowing how to hide your sources. (Einstein)

It would had been much help in first place had you said you want select and update in the "same query statement".Anyways its good you got what you wanted.

PBUH

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-05-25 : 11:30:27

quote:
Originally posted by trouble2

Yes, that seems correct, thanks!!!

The secret to creativity is knowing how to hide your sources. (Einstein)

No worries, glad I could help.

Julien.Crawford
Starting Member

21 Posts

Posted - 2010-05-25 : 18:54:17

This is how I do this.
It doesn't use new sqlserver features, just sql.

declare @s varchar(10)
declare @n int

update mytable
set @s = mytable.col1
, @n = mytable.col2
, col1 = 'aaa'
, col3 = 99
where ...

select @s, @n

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources