Solved

Delete by Row SQL Syntax

Posted on 2007-03-29
7
1,386 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

810 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