Chapter 7: Adding, Changing, and Deleting Data

Inserting data into some columns

You can use the select statement to add data to some, but not all, columns in a row just as you do with the values clause. Simply specify the columns to which you want to add data in the insert clause.

For example, some authors in the authors table do not have titles and, therefore, do not have entries in the titleauthor table. To pull their au_id numbers out of the authors table and insert them into the titleauthor table as placeholders, try this statement:

insert titleauthor (au_id)
select au_id
    from authors
    where au_id not in
    (select au_id from titleauthor)

This statement is not legal, because a value is required for the title_id column. Null values are not permitted and no default is specified. You can enter the dummy value “xx1111” for titles_id by using a constant, as follows:

insert titleauthor (au_id, title_id)
select au_id, "xx1111"
    from authors
    where au_id not in
    (select au_id from titleauthor)

The titleauthor table now contains four new rows with entries for the au_id column, dummy entries for the title_id column, and null values for the other two columns.

View this book as PDF