Adding and Manipulating Data

Jun 17, 2009 Author: MYSQL Expert

While most of the time you will be retrieving data from a database, being able to insert it is essential to using it, later. This is done by means of the INSERT statement, which takes on two forms:

INSERT INTO <tablename>
 VALUES 
(<field1value>[, ..., <fieldnvalue>])
INSERT INTO <tablename>
(<field1>[, ...,<fieldn> ])
VALUES
(<field1value>[, ..., <fieldnvalue>])

The first form of the INSERT statement is used when you want to provide values for every column in your table—in this case, the column values must be specified in the same order in which they appear in the table declaration. This formis almost never ideal; for one thing, you may not even be able to specify a value for each column—for example, some of the columns may be calculated automatically by the system, and forcing a value onto them may actually cause an error to be thrown. In addition, using this form implies that you expect the order of the columns to never change—this is never a good idea if you plan for your application to run for more than a month! In its second form, the INSERT statement consists of three main parts. The first part tells the database engine into which table to insert the data. The second part indicates the columns for which we’re providing a value; finally, the third part contains the actual data to insert. Here’s an example:

INSERT INTO book (isbn, title, author)
VALUES (’0812550706’, ’Ender\’s Game’, ’Orson Scott Card’);

Adding records to the database is, of course, not very useful without the ability to modify them. To update records, you can use the UPDATE statement, which can either alter the value of one or more columns for all rows, or for a specific subset thereof by means of a WHERE clause. For example, the following UPDATE statement updates the publisher for all records in the book table to a value of ’Tor Science Fiction.’

UPDATE book SET publisher = ’Tor Science Fiction’;

Since it is not likely that all books in the table will have the same publisher (and, if they did, you wouldn’t need a database column to tell you), you can further restrict the range of records over which the UPDATE statement operates:

UPDATE book
SET publisher = ’Tor Science Fiction’, author = ’Orson S. Card’
WHERE isbn = ’0812550706’;

This UPDATE statement will update only the record (or records) where isbn is equal to the value ’0812550706’. Notice also that this statement illustrates another feature of the UPDATE statement: it is possible to update multiple columns at a time using the same statement.


views 2903
  1. Add New Comment