Expression in SET - 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

General SQL Server Forums

New to SQL Server Programming

Expression in SET

Author

Topic

yesprasoon
Starting Member

2 Posts

Posted - 2007-02-07 : 00:47:12

I need a statement like:UPDATE tblQuestions SET QuestionNumber = QuestionNumber +1 WHERE QuestionNumber >=10 AND TestID = 1 AND SectionNumber = 1
(Required output is clear from statement itself)

I tried with
UPDATE tblQuestions SET QuestionNumber = EVAL(QuestionNumber +1) WHERE QuestionNumber >=10 AND TestID = 1 AND SectionNumber = 1
and
UPDATE tblQuestions SET QuestionNumber = (QuestionNumber +1) WHERE QuestionNumber >=10 AND TestID = 1 AND SectionNumber = 1

I've just started working with SQL. Please suggest correct approach.

drewgeezmoe
Starting Member

22 Posts

Posted - 2007-02-07 : 01:04:39

What is the problem with your syntax?
Is there an error occuring when you run your syntax?

Your future is made by the things you are presently doing.

yesprasoon
Starting Member

2 Posts

Posted - 2007-02-07 : 01:15:48

Not error..but wrong output.
If I write
UPDATE tblQuestions SET QuestionNumber = 7 WHERE QuestionNumber >=10 AND TestID = 1 AND SectionNumber = 1
then it changes the value to 7
if I write
UPDATE tblQuestions SET QuestionNumber = 6+1 WHERE QuestionNumber >=10 AND TestID = 1 AND SectionNumber = 1
then it changes the value to 6
and if i write
UPDATE tblQuestions SET QuestionNumber = (6+1) WHERE QuestionNumber >=10 AND TestID = 1 AND SectionNumber = 1
then it changes the value to 0
Problem is that I've to write value in expression mode, something like n+1

drewgeezmoe
Starting Member

22 Posts

Posted - 2007-02-07 : 02:35:29

quote:
Originally posted by yesprasoon

I need a statement like:UPDATE tblQuestions SET QuestionNumber = QuestionNumber +1 WHERE QuestionNumber >=10 AND TestID = 1 AND SectionNumber = 1
(Required output is clear from statement itself)

That is the format that you should follow in adding with the UPDATE function for now to avoid complex syntax.

Syntax
expression + expression

Arguments
expression

Is any valid Microsoft® SQL Server™ expression of any of the data types in the numeric category except the bit data type.
****reference: sql server books online

UPDATE <Table name>
SET <column name = column + expression or another column>
WHERE <search condition>

Your future is made by the things you are presently doing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources