Solved

Delete by Row SQL Syntax

Posted on 2007-03-29
7
1,383 Views
Last Modified: 2011-10-03
I'm trying to clean up a database and delete the last 700 rows, from 9270 to 9770.  How whould I call the rows to delete?

I could use this:
Delete from Companies where ID > 9270;

the ID's are not in order though.  I need to delete by row.  

Delete from Companies where (row) > 9270;

Please help thanks.
0
Comment
Question by:smoothcat11
  • 2
  • 2
7 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 18818230
>>last 700 rows

Last, ordered how?

Sequence is not something guaranteed in a relational DB -- except when you use an ORDER BY.

Once you determine what ORDER BY puts those 700 last, we can talk about the criteria to delete those rows.  It will probably be WHERE someField > Something -- and that will be apparent once you determine the ordering.
0
 

Author Comment

by:smoothcat11
ID: 18818311
It's ordered by the INSERT.  So if you add the record by a form it will put the last insert into the database.  Also, some of the records were deleted so the id is all over the place.  I know I have to delete the last 700 records that were inserted and I don't know the syntax to delete the last rows or even call a record by the row.  Thanks
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 18818449
Is it the ID that is between 9270 and 9770?

I get the idea it's not that easy ...

OK, what if you add a field with an autonumber / identity in it.  Does that come out in the order you want?  if so ... you're home free.
0
 

Author Comment

by:smoothcat11
ID: 18818722
I exported it to Excel then took out the last rows.

create temporary table temp (id int not null primary key auto_increment, data varchar(255)) select null, data from _template;
select * from temp;
drop table temp;

I got that from someone.  You can get it in order w/ that and then delete anything w/ ID > 9270

Thanks.
0
 
LVL 1

Accepted Solution

by:
SignsUS earned 500 total points
ID: 22087749
You almost have it:

Delete from Companies where ID >= 9270 AND ID <= 9770;

Done :)

It doesn't matter what order they are in.  It will delete according to those conditions.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now