Solved

Delete by Row SQL Syntax

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

Suggested Solutions

Title # Comments Views Activity
error in my cursor 5 41
Import MySQL data into MS Access using VB.Net interface 5 31
java mysql insert application 14 29
sql server query 12 26
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

828 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