[dba-SQLServer] Delete

Hi Wil,

Others will probably jump in but I think you could end up deleting more
than 5 rows. If there are multiple 5th top rows ie with the same date.
In SQL Server you would do something like...
DELETE FROM [tableName]
WHERE ID IN (  SELECT TOP 5 ID FROM [tableName] WHERE [tableName].
player = 1 ORDER BY playdate ASC )

I see you are using a Access recordset. This won't work. I don't think
you can run a query against a recordset. 

Why not just open the recordset sorted and loop 5 times deleting as you
go? I'm not sure but I seem to recall you don't need to movenext if you
are deleteing.

Cheers

Michael M 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Wil
Wakely
Sent: Monday, 8 August 2011 1:06 PM
To: sql-dba
Subject: [dba-SQLServer] Delete

Is this a correct sql statement to delete the first 5 rows (oldest
dates) of a recordset for Access 2002? I'm new to SQL.

strSQL1 = "DELETE FROM rs WHERE TOP 5,  player=1 ORDER BY playdate ASC;"
DoCmd.RunSQL strSQL1

==wilw

--
===wil wakely
sunny san diego ca
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1391 / Virus Database: 1520/3820 - Release Date: 08/07/11