Mod 10: Create / Update / Delete

The SQL data manipulation language (DML) is used to query and modify database data. In this chapter, we will describe how to use the INSERT, UPDATE, and DELETE SQL DML command statements, defined below.

• SELECT  – to query data in the database
• INSERT  – to insert data into a table
• UPDATE – to update data in a table
• DELETE – to delete data from a table

In the SQL DML statement:

• Each clause in a statement should begin on a new line.
• The beginning of each clause should line up with the beginning of other clauses.
• If a clause has several parts, they should appear on separate lines and be indented under the start of the clause to show the relationship.
• Upper case letters are used to represent reserved words.
• Lower case letters are used to represent user-defined words.

INSERT statement

The INSERT statement adds rows to a table. In addition,

• INSERT specifies the table or view that data will be inserted into.
• Column_list lists columns that will be affected by the INSERT.
• If a column is omitted, each value must be provided.
• If you are including columns, they can be listed in any order.
• VALUES specifies the data that you want to insert into the table. VALUES is required.
• Columns with the IDENTITY property should not be explicitly listed in the column_list or values_clause.

The syntax for the INSERT statement is:

When inserting rows with the INSERT statement, these rules apply:

• Inserting an empty string (‘ ‘) into a varchar or text column inserts a single space.
• All char columns are right-padded to the defined length.
• All trailing spaces are removed from data inserted into varchar columns, except in strings that contain only spaces. These strings are truncated to a single space.
• If an INSERT statement violates a constraint, default or rule, or if it is the wrong data type, the statement fails and SQL Server displays an error message.

When you specify values for only some of the columns in the column_list, one of three things can happen to the columns that have no values:

1. A default value is entered if the column has a DEFAULT constraint, if a default is bound to the column, or if a default is bound to the underlying user-defined data type.
2. NULL is entered if the column allows NULLs and no default value exists for the column.
3. An error message is displayed and the row is rejected if the column is defined as NOT NULL and no default exists.

This example uses INSERT to add a record to the publisher’s Authors table.

This following example illustrates how to insert a partial row into the Publishers table with a column list. The country column had a default value of Canada so it does not require that you include it in your values.

To insert rows into a table with an IDENTITY column, follow the below example. Do not supply the value for the IDENTITY nor the name of the column in the column list.

Inserting specific values into an IDENTITY column

By default, data cannot be inserted directly into an IDENTITY column; however, if a row is accidentally deleted, or there are gaps in the IDENTITY column values, you can insert a row and specify the IDENTITY column value.

To allow an insert with a specific identity value, the IDENTITY_INSERT option can be used as follows.

 Inserting rows with a SELECT statement

We can sometimes create a small temporary table from a large table. For this, we can insert rows with a SELECT statement. When using this command, there is no validation for uniqueness. Consequently, there may be many rows with the same pub_id in the example below.

This example creates a smaller temporary Publishers table using the CREATE TABLE statement. Then the INSERT with a SELECT statement is used to add records to this temporary Publishers table from the publis table.

In this example, we’re copying a subset of data.

In this example, the publishers’ data are copied to the tmpPublishers table and the country column is set to Canada.

UPDATE statement

The UPDATE statement changes data in existing rows either by adding new data or modifying existing data.

This example uses the UPDATE statement to standardize the country field to be Canada for all records in the Publishers table.

This example increases the royalty amount by 10% for those royalty amounts between 10 and 20.

Including subqueries in an UPDATE statement

The employees from the Employees table who were hired by the publisher in 2010 are given a promotion to the highest job level for their job type. This is what the UPDATE statement would look like.

DELETE statement

The DELETE statement removes rows from a record set. DELETE names the table or view that holds the rows that will be deleted and only one table or row may be listed at a time. WHERE is  a standard WHERE clause that limits the deletion to select records.

The DELETE syntax looks like this.

The rules for the DELETE statement are:

1. If you omit a WHERE clause, all rows in the table are removed (except for indexes, the table, constraints).
2. DELETE cannot be used with a view that has a FROM clause naming more than one table. (Delete can affect only one base table at a time.)

What follows are three different DELETE statements that can be used.

1. Deleting all rows from a table.

2. Deleting selected rows:

3. Deleting rows based on a value in a subquery: