Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.0Mb
PDF (A4) - 31.0Mb
PDF (RPM) - 29.3Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb
HTML Download (RPM) - 6.2Mb
Man Pages (TGZ) - 175.7Kb
Man Pages (Zip) - 286.0Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb
Excerpts from this Manual 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 '/path/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
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 Jason Chesterton on October 1, 2006
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
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
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.
Sign Up Login You must be logged in to post a comment.