Using SQL to delete all records from a specific row

[snanuwa]

Hi,

I have selected all the records from a table. “Select * from projects”;

I want to keep the 1st 60 records and delete the rest.

I always will want to delete records from the same row but the row can move up and down so I do not have a fixed reference point like a PK but it will always contain the same data in the first column I can reference.

Which is “Research”

Therefore, I need something that finds the first “Research” and delete all the records after it.

Any SQL statements will help me a lot!

Of if you need me to clarify more please ask

Any help, greatly Appreciated

Thanks

 

[Pat Hartman]

Tables are not flat files. Records do NOT necessarily maintain their original position. Your design needs to be reviewed because your tables are not normalized.

To RELIABLY accomplish your request the table will need an autonumber. You can then use two queries:
query1:
Select RecID From YourTable Where SomeField = "Research";

query2:
Delete * from YourTable Inner Join Query1 On YourTable.RecID >= Query1.RecID;

The second query uses a non-equi-join and so it cannot be displayed by the query designer. You will need to work with it in SQL view.