Discovering SQL: A Hands-On Guide for Beginners

INSERT, UPDATE, AND DELETE REVISITED

While the SELECT statement seems to draw all the attention of the end user, developers responsible for implementing business logic for the applications hold INSERT, UPDATE, and DELETE statements in equal respect. After all, they are concerned with getting the data in, managing it there as long as needed, and retiring it when the need is gone. Proper use of the statements is the hallmarks of a well-behaved database.

INSERT

We used INSERT in Chapter 1 and throughout Chapter 2, but as you have probably guessed, there is more to it.

The classic INSERT requires you to list all columns in the table and supply corresponding values for each column. For instance, the full insert into the BOOKS table might look like this:

INSERT INTO books
          (bk_id
           ‚bk_title
           ‚bk_ISBN
           ‚bk_publisher
           ‚bk_published_year
           ‚bk_price
           ‚bk_page_count
           ‚bk_bought_on
           ‚bk_hard_cover
           ‚bk_cover_pic
           ‚bk_notes)
     VALUES
           ( 1
           ‚‘SQL Bible’
           ‚‘978-0470229064’
           ‚‘Wiley’
           ‚2008
           ‚39.99
           ‚888
           ‚CAST(‘10-10-2009’ as smalldatetime)
           ‚0
           ‚NULL
           ‚NULL)

This is fairly intuitive: a list of columns (in any order), and a matching list of values (in matching order), formatted for appropriate data types (string, numbers, dates), and off we go. The shortened version of the same statement would get rid of the columns list:

INSERT INTO books
     VALUES
           ( 1
           ‚‘SQL Bible’
           ‚‘978-0470229064’
           ‚‘Wiley’
           ‚2008
           ‚39.99
           ‚888
           ‚CAST(‘10-10-2009’ as smalldatetime)
           ‚0
           ‚NULL
           ‚NULL)

While handy, this syntax imposes two major restrictions: ...