Updating a certain number of records only? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

Updating a certain number of records only?

Author

Topic

acribb
Starting Member

19 Posts

Posted - 2008-06-19 : 16:29:22

I need a query that will update only 99 records per user.

I have a specific list of users I need to update records for, so the end of the statement will be - "where username in ('123', '124', '125')".

Thanks for any help....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 02:28:05

Is there any field in table which has unqiue values for a given user?

acribb
Starting Member

19 Posts

Posted - 2008-06-20 : 08:43:07

The records I am querying have a user name field which I need to match up to my list of " where username in ('123', '1234', ..)" All of the users will have more than 100 records, so this will not be a problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 09:35:20

quote:
Originally posted by acribb

The records I am querying have a user name field which I need to match up to my list of " where username in ('123', '1234', ..)" All of the users will have more than 100 records, so this will not be a problem.

Nope i was asking if your table contain any field which will have unique values for a particular value of username.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-06-20 : 09:53:05

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

acribb
Starting Member

19 Posts

Posted - 2008-06-20 : 10:15:38

I am thinking the answer is no.

table a
-------
id
username
address
city
state
lastmodifieddate

Each of my usernames has over a 100 records in table a. For my test, I only need to update the lastmodifieddate, but only for 99 records of each username in my list.

Does this make more sense?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 10:26:05

quote:
Originally posted by acribb

I am thinking the answer is no.

table a
-------
id
username
address
city
state
lastmodifieddate

Each of my usernames has over a 100 records in table a. For my test, I only need to update the lastmodifieddate, but only for 99 records of each username in my list.

Does this make more sense?

is id an identity field?

acribb
Starting Member

19 Posts

Posted - 2008-06-20 : 11:19:53

Yes.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 11:25:41

quote:
Originally posted by acribb

Yes.

Update t
set t.Field1=value1,t.Field2=value2,..
FROM (SELECT (SELECT COUNT(*) FROM YourTable WHERE username =t.username AND id <t.id) + 1 AS Seq,*
FROM YourTable t 
WHERE t.username in ('123','124','125')
)t
WHERE t.Seq<100

acribb
Starting Member

19 Posts

Posted - 2008-06-20 : 13:24:26

Actually this doesn't work - I get the following error -

37000(4421)[Microsoft][ODBC SQL Server Driver][SQL Server]Derived table 't' is not updatable because a column of the derived table is derived or constant.
37000(8180)[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (0.02 secs)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 14:24:31

quote:
Originally posted by acribb

Actually this doesn't work - I get the following error -

37000(4421)[Microsoft][ODBC SQL Server Driver][SQL Server]Derived table 't' is not updatable because a column of the derived table is derived or constant.
37000(8180)[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (0.02 secs)

then try puttuing results onto temp table and updating

SELECT (SELECT COUNT(*) FROM YourTable WHERE username =t.username AND id <t.id) + 1 AS Seq,
id,
username,
address,
city, 
state 
INTO #Temp
FROM YourTable t 
WHERE t.username in ('123','124','125')

UPDATE t
SET t.field1=value1,
t.field2=value2,...
FROM YourTable t
INNER JOIN #Temp tmp
ON tmp.id=t.id
AND tmp.username=t.username

DROP TABLE #Temp
WHERE tmp.Seq<100

acribb
Starting Member

19 Posts

Posted - 2008-06-20 : 16:03:52

I'm getting the following error -
Incorrect syntax near the keyword 'WHERE'.

on "WHERE tmp.Seq<100"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-20 : 19:08:13

quote:
Originally posted by acribb

I'm getting the following error -
Incorrect syntax near the keyword 'WHERE'.

on "WHERE tmp.Seq<100"

You should not use where clause to drop a table
It is by mistake given by Visakh.

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-21 : 01:44:41

quote:
Originally posted by madhivanan

quote:
Originally posted by acribb

I'm getting the following error -
Incorrect syntax near the keyword 'WHERE'.

on "WHERE tmp.Seq<100"

You should not use where clause to drop a table
It is by mistake given by Visakh.

Madhivanan

Failing to plan is Planning to fail

Ah my copy paste is awful

. i overlapped the two statements what i meant was this:-

UPDATE t
SET t.field1=value1,
t.field2=value2,...
FROM YourTable t
INNER JOIN #Temp tmp
ON tmp.id=t.id
AND tmp.username=t.username
WHERE tmp.Seq<100

DROP TABLE #Temp

Thanks for spotting it Madhi

acribb
Starting Member

19 Posts

Posted - 2008-07-03 : 15:38:23

Works perfectly! Thanks for the help everyone...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources