[dba-SQLServer] Delete

Hello Michael and Wil,

Wil, thanks for that, I did not know that you could use TOP n in the where
clause, I only use it when I select.

Michael, thanks for highlighting the potential additional records, I am
looking forward to playing with TOP in where clauses,

Mark



On 8 August 2011 08:51, Michael Maddison <michael at ddisolutions.com.au>wrote:

> 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
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>