Solved

Delete by Row SQL Syntax

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 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

10 Experts available now in Live!

Get 1:1 Help Now