How can I delete a row from an SQL table?

Hey everyone....

1 - How can I delete (or remove) the first row from a SQL table? What command does this?

2 - If I do remove this first row, will all the other rows automatically move up? Or do I need to somehow move them myself?

3 - How can I add just one additional row (record) to the end of an existing SQL table? what SQL command can do this?

As you can tell, I did everything that backward way whereby I'd just delete the whole table and rewrite it again. That worked well, but it was WAY TO SLOW!

May everyone have a blessed day as the Lord wills.

JEP_Dude

 

Ahh, my favorite topic--the mystical order of data in tables.

1. There is no 'first' row in a table.

2. There's still no 'first' row in a table nor any 'subsequent' rows.

3. Just as there's no 'first' row, there's no 'last' row or 'end' of the table.

Tables have no order---only queries can have order and only when you define that order using the 'ORDER BY' keyword. Think of your tables as a bucket of all your data rows floating freely about one another. Queries are configurable nets to pull that data out just like you want. That means depending on the query you are using, one table can be put in order multiple ways (by a date field, by a numerical field, by a text field in reverse alphabetically order).

What you need to do is determine the field that you want to use to order your data--then configure your table so that you when you use the 'ORDER BY' keyword with that field in your query you can accomplish questions you had.

So what's the field that determines the order of your data and its datatype? May god have mercy on your soul if that field is an autonumber--that's my second favorite topic and I will be happy to get all preachy about that next.

 

Hi,
1,2 It will delete one the first row from table.

DELETE FROM `table_name` LIMIT 1

Code (markup):

3 It will insert one row in the end of table

INSERT INTO `table_name` (`field1`, field2) VALUES ("val1", "val2")

Code (markup):

 

DELETE FROM tablename WHERE field = '$variable'

 

Hey there....

Thanks everyone for such an informative replies!

I'm using Visual Studio 2008 with SQL 2008 to design a website. I only need the latest (most recent) set of 60 elements of price data. The real problem is, ... how can I QUICKLY detect the OLDEST row within a SQL table and delete it?

Plog: Thanks for clearing up a misconception of mine. I hope you can see my point as I desire to remove the oldest with the LEAST amount of processing as it MUST be fast! BTW, what is "autonumber"?

Layoutzzz: Thanks .... your suggestion looks promising! But what is "LIMIT 1"? How does it work?

redditor: I have confidence the suggestion that you've made will do he job. However, have can I QUICKLY calculate the business day that is exactly 60 days ago?


God Bless ...

JEP_Dude

 

LIMIT 1 limits the results of a query to one row only.

For example:
SELECT name FROM employees ORDER BY birthdate LIMIT 10;

This would show the first 10 names from the employees table ordered by their birthdate.

 

As I was researching online, I saw an intersesting use of SELECT. Yet, I'm use to flat files so I must ask what does "MaxHistory" do in a SELECT statement? Is that part of a standard SELECT command? ...or was it someones unique solution to their problem?

May you have a blessed day as the Lord wills.

JEP_Dude

 

Sounds unique to their problem. Can you post the entire SQL statement, maybe we can determine what it means from the context.

 

Hey Everyone ...!

I must ask someone for a comment regarding the use of "MaxHistory" in a SELECT statement.

God Bless...

JEP_Dude

----- Various Replies Below:

Redditor: DELETE FROM tablename WHERE field = '$variable' Does sound interesting, but what if I can't designate the specific row? For instance, since I have this record definition of StockSymbol, CurrentDate, and TradePrice? If those were the only columns defined, how could I reliably calculate the precise CurrentDatein order to DELETE the 6oth row as different months have different number of days? ... and what about Holidays?

Fruktkaka: "LIMIT 1" does sound to be VERY interesting! But would it only DELETE the oldest row?

Plog: Sorry, I can't post the SELECT command as I'm currently researching the problem areas of an ASP.Net 2008 Solution. I'm only familiar with flat files. So now I'm now learning the details of SQL. The key issue is, how can I only DELETE the 1st or the 60th row?

 

In order to delete the oldest or newest from a database you need to have either:

1. A unique field with an autoincremented number (called the_id in the example below)
2. A field with a timestamp (called the_timestamp in the example below)

You can then do:

DELETE FROM name_of_table ORDER BY the_id ASC LIMIT 1;
or
DELETE FROM name_of_table ORDER BY the_timestamp ASC LIMIT 1;

ORDER BY the_id ASC means that it orders in ascending (which is default so you could leave out the word ASC if you want to) order (1 - 2 - 3 - 4 - 5), so it would start with your first (oldest) row. And LIMIT 1 means it will only delete the first row.

If you'd go for ORDER BY the_id DESC instead it will order things in descending order (5 - 4 - 3 - 2 - 1) instead, thus removing your newest row if you LIMIT it with 1.

If you want to delete the 60th row you really need to have unique ID's incremented for each field. Then you'd do this:

DELETE FROM name_of_table WHERE the_id = 60;

But in the above line you need to have 60 rows, numbered from 1 to 60. If you have previously deleted one row then row 60 would actually be 59. The ID numbers don't automatically update if you remove a line. If that is the case you could go through the whole table in a php loop and then return the ID number of the 60th row and delete it.

 

Fruktkaka ....

Thanks for such an informed reply! Yet there are some terms that I'm not familiar with. Would you please be kind enough to describe both "an autoincremented number" and "a timestamp". How are they activated and assigned?

The flowing is an important question:
If the first column contains a RecordNumber of "1" and I DELETE that row (with LIMIT 1), would I need to somehow renumber the RecordNumbers of the rows that remain? ... or would the next row with the RecordNumber value of "2" that remains, automatically be changed to "1" or would it remain as "2"? ... what about the rest of the table?

If it would somehow automatically decrement all the table's RecordNumbers down by one that would make things easier.

May everyone have a blessed day as the Lord wills.

JEP_Dude

 

The "problem" is that relational databases doesn't really put any value to in which order you add the data. Internally the data isn't ordered at all, they only get ordered when you tell them to using ORDER BY etc in the SQL language.

You should have a read at Wikipedia for the basics of a relational database. It might be a bit much to grasp at first, but having the knowledge of the theories behind it makes it easier to learn how to use SQL at its best.

Every table in a Mysql database should generally have something called a primary key. This is what identifies that row as unique from the others.

The following SQL statement adds a new table called "Test" with two fields called "the_id" and "the_text". The field the_id is set as the primary key and it is set as auto_increment.

CREATE TABLE `test` (
`the_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`the_text` VARCHAR( 255 ) NOT NULL
);

If you now add data to this:
INSERT INTO `test` (`the_id`, `the_text`) VALUES (NULL , 'Field 1');
You see that the_id gets sent a NULL instead of a value. This is because that field is auto_incremented so we don't need to give it a value. It will automatically be 1 in this case. If you add more data it will become 2, 3, 4 etc.

If you add 5 rows, (Field 1, Field 2 etc) and then delete the row with ID 4 and then decide to add another row it will get ID number 6, eventhough there's now only four rows left. This isn't really a problem though. Say you want to show the_text of the fourth oldest row. ID 4 is now gone, so it's ID 5 we're looking for. The really important thing is that MySQL starts counting rows from 0, not from 1, so Row 4 for us humans that use 1 as the first number is Row 3 for MySQL. We can now use the LIMIT again:

SELECT * FROM `test` ORDER BY id LIMIT 3, 1;

LIMIT 3, 1 means that you start at row 3 (which is really the fourth row) and limit the results to 1 row, this will list the_id 5. If you put in LIMIT 3, 2 it would show the_id 5 and the_id 6.

With some PHP you could probably rearrange the auto_incremented numbers but you really shouldn't. Let MySQL give the rows their unique ID number and you can still do everything you wish through SQL.

 

Fruktkaka .... & others now just tuning in...

Thanks to each one of you for taking the time to explain to me the details that I lack.

I am familiar with relational databases, but I was not ware SQL was relational. I must have spent to much time with flat files.

The example you gave of "AUTO_INCREMENT PRIMARY KEY" and "LIMIT x,y" sound very interesting. After pondering the project details, I don't think I will need to DELETE the oldest row in a stock price table. However, I must remove the newest from a fixed 60 row table. What about:

DELETE FROM TableName LIMIT 60, 1

Would that delete the most recently added row? If it will not, how could I detect a Time Stamped column to remove the most recent row?

May each of you have blessed day as the Lord wills.

JEP_Dude

 

You must order it with some field first as you can't count on the rows being in any particular order as default. If you have a column with a unique auto incremented value (the newst field would then have the highest value) or a column with a timestap (which would then be the most recent timestamp) you need to order it by that first. Otherwise you might end up deleting something more or less at random.

Could you list the fields you have in the table at the moment?

By the way, if you are not 100% confident in your DELETE query, you could always replace DELETE with SELECT * to see that you're really are getting the proper line before trying to delete it.

 

Fruktkaka ...& others just now tuning in..

Ah... so it needs to be ordered. Then what about? ...

DELETE FROM TableName ORDER BY PrimaryKey ASCENDING LIMIT 60, 1

Or am I mixing together two commands?

I can only give a partial realistic table definition as the project is still under development. However, it should be something like this:

PrimaryKey Integer
CloseDate Date
StockSymbol String
OpenPrice Float or Currency
HighPrice Float or Currency
LowPrice Float or Currency
ClosePrice Float or Currency

I was considering just replacing the whole table during the night and just replacing the most recent row every minute of the next business day. But that just once more restates the problem of how can I just DELETE today's, more recent row? Can I just remove or DELETE the row with the highest valued PrimaryKey or the most recent CloseDate and replace with a new version? If I did it that way, I would effectively maintain the most recent 60 closing prices!

May each of you have blessed day as the Lord wills.

JEP_Dude