Select 50 rows - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Select 50 rows

Author

Topic

TJ
Posting Yak Master

201 Posts

Posted - 2002-07-19 : 17:51:12

Will someone point me in the right direction? How do I get a query to return 50 rows in a select? I need to select them, then update a column. I'm obviously not asking the right question when I go to BOL because I haven't been able to find anything to help me.

Thanks!
Teresa

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-07-19 : 18:03:59

Look for TOP or rowcount in BOL

select top 50 * from ogrish order by aleb

or

set rowcount 50
select * from ogrish order by aleb

TJ
Posting Yak Master

201 Posts

Posted - 2002-07-19 : 18:09:14

quote:

Look for TOP or rowcount in BOL

select top 50 * from ogrish order by aleb

or

set rowcount 50
select * from ogrish order by aleb

I've been working with this code, but I'm not getting any records returned.

select intsaleman, diarystat 
from (select top 100 *  from prospect order by intsaleman) as t1
where t1.intsaleman = 'adc' and t1.diarystat = 'ac'

this code returns 100 records, but it returns every field:

select top 100 * from prospect where intsaleman = 'adc' and diarystat = 'ac'

I need to update one field in the first 100 records that meet the criteria I'm looking for.

Thanks for your help!

Garth
SQLTeam Author

119 Posts

Posted - 2002-07-19 : 18:09:29

Something like this might work if the update is static.

UPDATE TableName
SET ColName=@ColName
WHERE PrimaryKey
IN (SELECT TOP 50 PrimaryKey FROM TableName WHERE...)

Garth
www.SQLBook.com

TJ
Posting Yak Master

201 Posts

Posted - 2002-07-19 : 18:21:00

quote:

Something like this might work if the update is static.

UPDATE TableName
SET ColName=@ColName
WHERE PrimaryKey
IN (SELECT TOP 50 PrimaryKey FROM TableName WHERE...)

Garth
www.SQLBook.com

The field I need to change isn't a primary key. How will that affect what I'm trying to do?

I have this code, but it changed 460 rows instead of 100



UPDATE prospect
SET intsaleman = 'MAS' 
FROM (SELECT TOP 100 * FROM prospect ORDER BY intsaleman) AS t1
WHERE prospect.intsaleman = 'ADC' and prospect.diarystat = 'AC'

Edited by - tj on 07/19/2002 18:24:48

Garth
SQLTeam Author

119 Posts

Posted - 2002-07-19 : 18:46:56

You need to replace [PrimaryKeyOfProspect] with the column that is defined as the primary key.

UPDATE prospect
SET intsaleman = 'MAS'
WHERE [PrimaryKeyOfProspect]
IN (SELECT TOP 100 [PrimaryKeyOfProspect] FROM prospect WHERE intsaleman = 'ADC' and diarystat = 'AC' ORDER BY intsaleman)

Edited by - garth on 07/19/2002 18:54:41

TJ
Posting Yak Master

201 Posts

Posted - 2002-07-22 : 10:28:13

quote:

You need to replace [PrimaryKeyOfProspect] with the column that is defined as the primary key.

UPDATE prospect
SET intsaleman = 'MAS'
WHERE [PrimaryKeyOfProspect]
IN (SELECT TOP 100 [PrimaryKeyOfProspect] FROM prospect WHERE intsaleman = 'ADC' and diarystat = 'AC' ORDER BY intsaleman)

Edited by - garth on 07/19/2002 18:54:41

That worked wonderfully! Thank you!

Would you please explain to me how this works? Where would I have found this information had I not posted here?

Teresa

"Wise sayings often fall on barren ground,
but a kind word is never thrown away."

Garth
SQLTeam Author

119 Posts

Posted - 2002-07-22 : 14:31:09

The IN clause will work with either a comma-separated list or the resultset of the query. In this case, I created a resultset that contained the primary key values of the rows you wanted to update.

The SQL Server Books Online (BOL) has some information on both IN and TOP. Search for "IN" and "Limiting Result Sets Using Top and Percent"

Garth
www.SQLBook.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources