SQL syntax to increment field value by 1?

24 Jan 2006 5:58 PM

VB Programmer

What is the SQL Express SQL syntax to increment a field by 1?

The stored proc doesn't like this:

CREATE PROCEDURE dbo.VideoViewIncrement
@VideoId nchar(40),
AS
UPDATE Videos SET TimesViewed = TimesViewed + 1 WHERE (VideoId = @VideoId)
RETURN

Any ideas?

Thanks.

24 Jan 2006 6:21 PM

Sericinus hunter

VB Programmer wrote:

> What is the SQL Express SQL syntax to increment a field by 1?
>
> The stored proc doesn't like this:
>
> CREATE PROCEDURE dbo.VideoViewIncrement
> @VideoId nchar(40),
> AS
> UPDATE Videos SET TimesViewed = TimesViewed + 1 WHERE (VideoId = @VideoId)
> RETURN

The following works for me on SQL Server 2000

UPDATE Videos SET TimesViewer = ((SELECT TimesViewed FROM Videos WHERE VideoId = @VideoId) + 1) WHERE VideoId = @VideoId

24 Jan 2006 6:31 PM

Patrice

UPDATE Videos SET TimesViewed = TimesViewed + 1 WHERE (VideoId = @VideoId)
is simpler and correct...

For now I suspect an extra comma before the AS keyword to cause a syntax
error message...

--
Patrice

Show quote

"Sericinus hunter" <serh***@flash.net> a écrit dans le message de
news:DeuBf.7043$NS6.232@newssvr30.news.prodigy.com...
> VB Programmer wrote:
> > What is the SQL Express SQL syntax to increment a field by 1?
> >
> > The stored proc doesn't like this:
> >
> > CREATE PROCEDURE dbo.VideoViewIncrement
> > @VideoId nchar(40),
> > AS
> > UPDATE Videos SET TimesViewed = TimesViewed + 1 WHERE (VideoId =
@VideoId)
> > RETURN
>
> The following works for me on SQL Server 2000
>
> UPDATE Videos SET TimesViewer = ((SELECT TimesViewed FROM Videos WHERE

VideoId = @VideoId) + 1) WHERE VideoId = @VideoId

24 Jan 2006 6:25 PM

Patrice

Please please please always tells us what is the exact result (error message
?) you get instead of just saying us it "doesn't like"...

For now I see an extra comma after the parameter declaration ? Is this in
your code ?

Do you have an "Incorrect syntax near the keyword 'AS'." message ?

--
Patrice

Show quote

"VB Programmer" <d***@emailme.com> a écrit dans le message de
news:uCSq7%23QIGHA.3036@tk2msftngp13.phx.gbl...
> What is the SQL Express SQL syntax to increment a field by 1?
>
> The stored proc doesn't like this:
>
> CREATE PROCEDURE dbo.VideoViewIncrement
> @VideoId nchar(40),
> AS
> UPDATE Videos SET TimesViewed = TimesViewed + 1 WHERE (VideoId =
@VideoId)
> RETURN
>
> Any ideas?
>
> Thanks.
>
>

24 Jan 2006 6:30 PM

VB Programmer

Thanks Patrice. You are correct....

Show quote

"Patrice" <a@bc.c> wrote in message
news:uj%23WTORIGHA.1388@TK2MSFTNGP11.phx.gbl...
> Please please please always tells us what is the exact result (error
> message
> ?) you get instead of just saying us it "doesn't like"...
>
> For now I see an extra comma after the parameter declaration ? Is this in
> your code ?
>
> Do you have an "Incorrect syntax near the keyword 'AS'." message ?
>
> --
> Patrice
>
> "VB Programmer" <d***@emailme.com> a écrit dans le message de
> news:uCSq7%23QIGHA.3036@tk2msftngp13.phx.gbl...
>> What is the SQL Express SQL syntax to increment a field by 1?
>>
>> The stored proc doesn't like this:
>>
>> CREATE PROCEDURE dbo.VideoViewIncrement
>> @VideoId nchar(40),
>> AS
>> UPDATE Videos SET TimesViewed = TimesViewed + 1 WHERE (VideoId =
> @VideoId)
>> RETURN
>>
>> Any ideas?
>>
>> Thanks.
>>
>>
>
>