O'Reilly logo

Discovering SQL: A Hands-On Guide for Beginners by Alex Kriegel

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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: ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required