Add or Modify an Entry in a MySQL Table

Problem


Add, modify or delete an entry in a MySQL table.

tl;dr


Add an entry:

insert into stocks (id, name, ticker, shares, price) values (null, "Microsoft", "MSFT", "5", "46.24");

Modify an entry:

update stocks set `shares`="995' where ticker="MSFT"

Delete an entry:

DELETE from stocks where name="Amazon";

Solution

Assuming you have created a MySQL table like the one in my previous how-to guide (Create a MySQL Database Table). But the point of a database is not only in adding new entries to it, but being able to modify existing ones on the fly.

An example table can be seen here:

mysql> select * from stocks;
+----+-----------+--------+--------+--------+
| ID | name | ticker | shares | price |
+----+-----------+--------+--------+--------+
| 1 | Microsoft | MSFT | 5 | 46.24 |
| 2 | Amazon | AMZN | 30 | 290.74 |
| 3 | Google | GOOG | 71 | 508.08 |
| 4 | Microsoft | MSFT | 71 | 48.11 |
| 5 | Microsoft | MSFT | 22 | 42.10 |
+----+-----------+--------+--------+--------+

Add an entry

Adding an entry to the table has already been addressed in the last tutorial, but here's a little refresher anyway.

Format depends columns and specifies an entry to each column, such as:

insert into [table name] (category1, category2, etc) values ("value1", "value2", etc);

Specific example:

insert into stocks (id, name, ticker, shares, price) values (null, "Microsoft", "MSFT", "5", "46.24");

Modify/update an entry

Update of a row/entry in a MySQL table has the following format:

update [table name] set [element]="newvalue" where [other element].[category]="value2";

Suppose you originally bought 995 shares of Microsoft at $46.24 each, but accidentally entered the total number as 5?

update stocks set shares="995" where price="46.24";

Or, suppose you remember that it was transaction ID #1, but don't remember what the exact price was at the time?

update stocks set shares="995" where id="1";

You can delete or modify entries by specifying any element in the table. If you update or delete a row element that is present in more than one row, it will delete all rows with the element in question. For example, specifying where name="Microsoft" will delete all rows that have "Microsoft" in them. To delete a specific entry, you need a way of identifying it, which is why an incrementing ID is so important. You may often have multiple identical rows, but each one refers to a distinct event.

Delete an entry

Deleting an entry works much the same way as updating it. You specify a row by an element that is present in it, and then tell MySQL to delete, or drop it.

General format:

delete from [table name] where [column name]=[field text];

For example, to delete Amazon from the table above:

mysql> delete from stocks where id="2";

Your table will now look like this:

mysql> select * from stocks;
+----+-----------+--------+--------+--------+
| ID | name | ticker | shares | price |
+----+-----------+--------+--------+--------+
| 1 | Microsoft | MSFT | 995 | 46.24 |
| 3 | Google | GOOG | 71 | 508.08 |
| 4 | Microsoft | MSFT | 71 | 48.11 |
| 5 | Microsoft | MSFT | 22 | 42.10 |
+----+-----------+--------+--------+--------+

Note that row IDs have not been updated, and ID #2 (Amazon) is missing entirely. You cannot undelete entries, although you can add identical ones by using the insert into command and specifying all fields required fields manually.

insert into stocks (id, name, ticker, shares, price) values ("2", "Amazon", "AMZN", "30", "290.74");

This will append the entry for Amazon at the end of the table, although it will not reset the ID counter. New entries will still have the correct ID applied to them. Note the "2" in place of null value for id.

mysql> select * from stocks;
+----+-----------+--------+--------+--------+
| ID | name | ticker | shares | price |
+----+-----------+--------+--------+--------+
| 1 | Microsoft | MSFT | 995 | 46.24 |
| 3 | Google | GOOG | 71 | 508.08 |
| 4 | Microsoft | MSFT | 71 | 48.11 |
| 5 | Microsoft | MSFT | 22 | 42.10 |
| 2 | Amazon | AMZN | 30 | 290.74 |
+----+-----------+--------+--------+--------+

You can also delete multiple rows at the same time by specifying a different parameter:

mysql> delete from stocks where ticker="MSFT";
Query OK, 3 rows affected (0.00 sec)

Note the 3 rows affected - this tells you how many entries were deleted (or modified).


Was this article helpful?

mood_bad Dislike 1
mood Like 1
visibility Views: 6411