Skip navigation links
3.3.4.2 Selecting Particular Rows »
Section Navigation      [Toggle]

3.3.4.1. Selecting All Data

The simplest form of SELECT retrieves everything from a table: 

mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+

This form of SELECT is useful if you want to review your entire table, for example, after you've just loaded it with your initial data set. For example, you may happen to think that the birth date for Bowser doesn't seem quite right. Consulting your original pedigree papers, you find that the correct birth year should be 1989, not 1979.

There are at least two ways to fix this:

  • Edit the file pet.txt to correct the error, then empty the table and reload it using DELETE and LOAD DATA: 

    mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet;

    However, if you do this, you must also re-enter the record for Puffball.

  • Fix only the erroneous record with an UPDATE statement: 

    mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

    The UPDATE changes only the record in question and does not require you to reload the table.


User Comments

Posted by Vlad Dogaru on May 11 2006 4:33pm[Delete] [Edit]

Or, if, for instance, you added an extra blank line in pet.txt and ended up with an extra empty row, you can use:

DELETE FROM pet WHERE name='';

Posted by Tyler Van Hensbergen on August 15 2006 7:02pm[Delete] [Edit]

You can also update multiple columns by using commas like so:
update pet set species = 'ferret', owner = 'Jimmy' where name = 'Bowser';

Posted by Jason Chesterton on October 1 2006 6:57am[Delete] [Edit]

Warning about the UPDATE statement:

If you forget the WHERE clause, you'll end up changing ALL the rows of the table.

Example: UPDATE pet SET birth = '2002-08-30';

(above line changes all pets' birthdates in the table to 2002-08-30)

Posted by Vasanth Rajendran on October 30 2007 2:38pm[Delete] [Edit]

fyi: if there are two or more rows which match the query, all of them are affected. So if there are two entries for Bowser, then both of them get affected.

This may lead to errors where the user changes more than what he bargained for. However, we can figure out from the number of rows affected that the change has impacted many entries instead of just one.

Posted by Clive Norman on January 10 2010 6:08pm[Delete] [Edit]

It is a good idea to first run a select to see which data is going to be affected, and then "comment out"/replace the select statement with the update\delete statement.

Add your own comment.