Steve!
I would suggest you some reading ( at least in a help file of T-SQL) about
cursors.
I was in your position once - very afraid of cursors and didn't have a clue
what they do. 
Actually cursor IS a recordset, and you could find it very beneficial to use
it in a lot of cases and do a lot of SQL programming thru the cursors .
As for your question, as with recordset you have to loop thru the records
and do the updating for each record as in previous example (same as in
recordset). 
Hope it helps.
	Eva

  


-----Original Message-----
From: Steve Carter [mailto:Steve.Carter@t...]
Sent: Thursday, October 25, 2001 5:10 AM
To: sql language
Subject: [sql_language] Re: update just the first few rows?


This is great, it does the trick.  Now for the real challenge: Can this (and
if so, how can it) also return a recordset?  So I want similar semantics to
the following (written in FantasySQL :-) )

SELECT TOP 4 ID, blah blah FROM myTable ORDER BY TimeStamp UPDATING THE
TIMESTAMP OF EACH RECORD YOU RETURN

> -----Original Message-----
> From: Zadoyen, Eva [mailto:EZadoyen@s...]
> Sent: 24 October 2001 15:16
> To: sql language
> Subject: [sql_language] Re: update just the first few rows?
> 
> 
> Sorry, remove the desc from "order by Time_Stamp desc"
> Should be:
> 
> **************************************
> DECLARE set_timestamp_cursor  cursor
> 	for select ID
>                  from myTable order by Time_Stamp
> ********************************************************
> 	Eva
> 
> 
> -----Original Message-----
> From: Zadoyen, Eva 
> Sent: Wednesday, October 24, 2001 10:09 AM
> To: 'sql language'
> Subject: RE: [sql_language] Re: update just the first few rows?
> 
> 
> Steve,
> I think you need to use a cursor with counter.
>  See the sample:
> *******************************************
> 
> 
> DECLARE  @id int,
> 	   @count int
> 
> DECLARE set_timestamp_cursor  cursor
> 	for select ID
>                  from myTable order by Time_Stamp desc
> OPEN set_timestamp_cursor
> set nocount on
> 
> FETCH next from set_timestamp_cursor into @id
> set @count =1		
> WHILE @@FETCH_STATUS = 0 and  @count <= 4 
> BEGIN
> 	
> 	UPDATE myTable SET Time_Stamp = getdate() WHERE ID =@id
> 
> select @count = @count +1
> 
> FETCH next from set_timestamp_cursor into @id
> 
> END
> CLOSE set_timestamp_cursor
> DEALLOCATE set_timestamp_cursor
> *************************************************8
> 
> Good luck!
> 	Eva
> 
> 
> 
> -----Original Message-----
> From: Steve Carter [mailto:Steve.Carter@t...]
> Sent: Wednesday, October 24, 2001 4:41 AM
> To: sql language
> Subject: [sql_language] Re: update just the first few rows?
> 
> 
> Thanks for the suggestion, but there may be 25 'smallest' 
> timestamps since
> they are only recorded to the second.  There is no sense of 
> uniqueness about
> the timestamps
> 
> > -----Original Message-----
> > From: Mike [mailto:mcooper@p...]
> > Sent: 23 October 2001 19:18
> > To: sql language
> > Subject: [sql_language] Re: update just the first few rows?
> > 
> > 
> > Steve,
> > Try selecting the 4 smallest timestamps in to a temp table 
> to do any 
> > modifications to the data you need, then update the rows in 
> > myTable by 
> > joing back on your temp table.
> > 
> > 
> > > I want to do something like this:
> > > 
> > > UPDATE myTable SET Time_Stamp = getdate() WHERE ID IN (
> > >   SELECT TOP 4 ID FROM myTable ORDER BY Time_Stamp )
> > > 
> > > I'm using SQL Server 6.5 and TOP isn't allowed.  I can get
> > > round that by using ROWCOUNT but ORDER BY isn't allowed in
> > > this circumstance either.  I can't work out how to get the 
> > > query to modify only the 4 records with the smallest time-
> > > stamps.  If there are more than 4 records sharing the same 
> > > smallest timestamp then I just want an arbitrary 4 of them.
> > > 
> > > Cheers,
> > > 
> > > Steve