Newbie. Needing help with increment

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

Newbie. Needing help with increment

Author

Topic

HitenB
Starting Member

11 Posts

Posted - 2008-07-14 : 10:26:08

Hi,

I'm a newbie on this forum. Glad to join you,

My first question:

I have a table in which i would like to insert the numbers between 1 to 207. So basically I would like to insert the value 1 until 207 where the HATSNumberField > 1310

Thanks for any help
Much appreciated

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-14 : 10:28:44

Update or Insert?

E 12°55'05.25"
N 56°04'39.16"

HitenB
Starting Member

11 Posts

Posted - 2008-07-14 : 10:30:05

Update would be more useful.

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-14 : 10:39:27

UPDATE f
SET f.Number = f.Seq
FROM (
SELECT Number, ROW_NUMBER() OVER (ORDER BY SomeColumn) AS Seq
FROM Table1
WHERE HATSNumberField > 1310)
AS f
WHERE f.Seq BETWEEN 1 AND 207

E 12°55'05.25"
N 56°04'39.16"

HitenB
Starting Member

11 Posts

Posted - 2008-07-14 : 10:49:04

Thanks Peso,

I tried that, but it doesnt seem to work. Dont know what i'm doing wrong. The table name is tblActions, the row that needs the update is called ActionNo and the only unique column by which im making the selection is called HATSActionNo.

So I want to update all HATSActionNo's greater than 1310 and give each action a number starting from one until 207.

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-14 : 10:54:41

[code]UPDATE f
SET f.ActionNo = f.Seq
FROM (
SELECT ActionNo,
ROW_NUMBER() OVER (ORDER BY HATSActionNo) AS Seq
FROM tblActions
WHERE HATSActionNo > 1310)
) AS f
WHERE f.Seq BETWEEN 1 AND 207[/code]

E 12°55'05.25"
N 56°04'39.16"

HitenB
Starting Member

11 Posts

Posted - 2008-07-14 : 11:16:00

Thanks.. I tried that and I keep getting (Missing operator) In Query f.Seq Error.
I've checked all the field names, etc and they're all correct. I dont see what i may be doing wrong.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-14 : 11:18:25

remove the ) after 1310

KH
[spoiler]Time is always against us[/spoiler]

HitenB
Starting Member

11 Posts

Posted - 2008-07-14 : 11:24:05

UPDATE f
SET f.ActionNo = f.Seq
FROM (SELECT ActionNo,ROW_NUMBER() OVER (ORDER BY HATSRecordNo) AS Seq FROM tblActions WHERE HATSRecordNo > 1310 AS f WHERE f.Seq BETWEEN 1 AND 207

Thanks khtan.. i'm still getting the same error after removing the )

HitenB
Starting Member

11 Posts

Posted - 2008-07-14 : 11:24:06

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-14 : 11:27:36

[code]
UPDATE f
SET f.ActionNo = f.Seq
FROM (
SELECT ActionNo,
ROW_NUMBER() OVER (ORDER BY HATSActionNo) AS Seq
FROM tblActions
WHERE HATSActionNo > 1310
) AS f
WHERE f.Seq BETWEEN 1 AND 207[/code]

KH
[spoiler]Time is always against us[/spoiler]

HitenB
Starting Member

11 Posts

Posted - 2008-07-14 : 11:34:06

I should mention that im using this in ms access 2003 as a query - union query, will that make a difference to the statement? I seem to be getting the same error.

HitenB
Starting Member

11 Posts

Posted - 2008-07-14 : 11:34:06

I should mention that im using this in ms access 2003 as a query - union query, will that make a difference to the statement? I seem to be getting the same error.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-14 : 11:57:35

If you use Access, why post in a SQL forum?
There are ACCESS forums on this site.

E 12°55'05.25"
N 56°04'39.16"

HitenB
Starting Member

11 Posts

Posted - 2008-07-15 : 03:10:43

As you know, I'm new to this so take it easy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources