There are three SQL statements that enable you to manipulate the data within a database's table.
• The INSERT statement
• The UPDATE statement
• The DELETE statement
One reason that SQL provides data manipulation statements is that it is primarily used within application programs that enable the user to edit the data using the application's own tools. The SQL programmer needs to be able to return the data to the database using SQL. In addition, most large-scale database systems are not designed with the database designer or programmer in mind. Because these systems are designed to be used in high-volume, multi-user environments, the primary design emphasis are placed on the query optimizer and data retrieval engines.
NOTE:Please note the minor differences in the appearance of commands and the way data is displayed in the various implementations.
The INSERT Statement
The INSERT statement enables you to enter data into the database. It can be broken down into two statements:
The INSERT...VALUES Statement
The INSERT...VALUES statement enters data into a table one record at a time. It is useful for small operations that deal with just a few records. The syntax of this statement is as follows:
INSERT INTO table-name (col1, col2...) VALUES (value1, value2...)
The basic format of the INSERT...VALUES statement adds a record to a table using the columns you give it and the corresponding values you instruct it to add. You must follow three rules when inserting data into a table with the INSERT...VALUES statement:
• The values used must be the same data type as the fields they are being added to.
• The data's size must be within the column's size. For instance, you cannot add an 80-character string to a 40-character column.
• The data's location in the VALUES list must correspond to the location in the column list of the column it is being added to. (That is, the first value must be entered into the first column, the second value into the second column, and so on.)
SQL> INSERT INTO COLLECTION (ITEM, WORTH, REMARKS)
VALUES('SUPERMANS CAPE', 250.00, 'TUGGED ON IT');
The INSERT statement does not require column names. If the column names are not entered, SQL lines up the values with their corresponding column numbers. In other words, SQL inserts the first value into the first column, the second value into the second column, and so on.
Inserting NULL Values
For now, all you need to know is that when a column is created, it can have several different limitations placed upon it. One of these limitations is that the column should (or should not) be allowed to contain NULL values. A NULL value means that the value is empty. It is neither a zero, in the case of an integer, nor a space, in the case of a string. Instead, no data at all exists for that record's column. If a column is defined as NOT NULL (that column is not allowed to contain a NULL value), you must insert a value for that column when using the INSERT statement. The INSERT is canceled if this rule is broken, and you should receive a descriptive error message concerning your error.
WARNING: You could insert spaces for a null column, but these spaces will be treated as a value. NULL simply means nothing is there.
SQL> INSERT INTO COLLECTION VALUES('SPORES MILDEW FUNGUS',50.00,NULL);
NOTE: Notice the syntax. Number data types do not require quotes; NULL does not require quotes; character data types do require quotes.
Inserting Unique Values
Many database management systems also allow you to create a UNIQUE column attribute. This attribute means that within the current table, the values within this column must be completely unique and cannot appear more than once. This limitation can cause problems when inserting or updating values into an existing table, as the following exchange demonstrates:
ANSI SQL does not offer a solution to this problem, but several commercial implementations include extensions that would allow you to use something like the following:
IF NOT EXISTS (SELECT * FROM COLLECTION WHERE NAME = 'STRING'
INSERT INTO COLLECTION VALUES('STRING', 50, 'MORE STRING'))
This particular example is supported in the Sybase system.A properly normalized table should have a unique, or key, field. This field is useful for joining data between tables, and it often improves the speed of your queries when using indexes.
The INSERT...SELECT Statement
The INSERT...VALUES statement is useful when adding single records to a database table, but it obviously has limitations. Would you like to use it to add 25,000 records to a table? In situations like this, the INSERT...SELECT statement is much more beneficial. It enables the programmer to copy information from a table or group of tables into another table. You will want to use this statement in several situations. Lookup tables are often created for performance gains. Lookup tables can contain data that is spread out across multiple tables in multiple databases. Because multiple-table joins are slower to process than simple queries, it is much quicker to execute a SELECT query against a lookup table than to execute a long, complicated joined query. Lookup tables are often stored on the client machines in client/server environments to reduce network traffic.
Many database systems also support temporary tables. Temporary tables exist for the life of your database connection and are deleted when your connection is terminated. The INSERT...SELECT statement can take the output of a SELECT statement and insert these values into a temporary table.
SQL> insert into tmp-tbl select * from table;
You are selecting all the rows that are in table and inserting them into tmp-tbl.
NOTE: Not all database management systems support temporary tables. Check the documentation for the specific system you are using to determine if this feature is supported.
The syntax of the INSERT...SELECT statement is as follows:
INSERT INTO table-name (col1, col2...) SELECT col1, col2... FROM tablename
Essentially, the output of a standard SELECT query is then input into a database table. The same rules that applied to the INSERT...VALUES statement apply to the INSERT...SELECT statement.
NOTE: The data appears to be in the table; however, the transaction is not finalized until a COMMIT is issued. The transaction can be committed either by issuing the COMMIT command or by simply exiting. See Day 11 for more on the COMMIT command.
The INSERT...SELECT statement requires you to follow several new rules:
• The SELECT statement cannot select rows from the table that is being inserted into.
• The number of columns in the INSERT INTO statement must equal the number of columns returned from the SELECT statement.
• The data types of the columns in the INSERT INTO statement must be the same as the data types of the columns returned from the SELECT statement.
Another use of the INSERT...SELECT statement is to back up a table that you are going to drop, truncate for repopulation, or rebuild. The process requires you to create a temporary table and insert data that is contained in your original table into the temporary table by selecting everything from the original table. For example:
SQL> insert into copy-table select * from original-table;
Now you can make changes to the original table with a clear conscience.
NOTE: Below you learn how to input data into a table using data from another database format. Nearly all businesses use a variety of database formats to store data for their organizations. The applications programmer is often expected to convert these formats, and you will learn some common methods for doing just that.
The UPDATE Statement
The purpose of the UPDATE statement is to change the values of existing records. The syntax is
UPDATE table-name SET
columnname1 = value1 [, columname2 = value2]...
This statement checks the WHERE clause first. For all records in the given table in which the WHERE clause evaluates to TRUE, the corresponding value is updated.
SQL> UPDATE COLLECTION SET WORTH = 900 WHERE ITEM = 'STRING';
SQL> update collection set worth = 900, item=ball where item ='STRING';
NOTE: Notice in the set that 900 does not have quotes, because it is a numeric data type. On the other hand, String is a character data type, which requires the quotes.
WARNING: If you omit the WHERE clause from the UPDATE statement, all records in the given table are updated.
Some database systems provide an extension to the standard UPDATE syntax. SQL Server's Transact-SQL language, for instance, enables programmers to update the contents of a table based on the contents of several other tables by using a FROM clause. The extended syntax looks like this:
UPDATE table-name SET columnname1 = value1 [, columname2 = value2]...
FROM table-list WHERE search-condition
SQL> UPDATE COLLECTION SET WORTH = WORTH * 0.005;
The UPDATE statement can also update columns based on the result of an arithmetic -expression. When using this technique, remember the requirement that the data type of the result of the -expression must be the same as the data type of the field that is being modified. Also, the size of the value must fit within the size of the field that is being modified.
Two problems can result from the use of calculated values: truncation and overflow. Truncation results when the database system converts a fractional number to an integer, for instance. Overflow results when the resulting value is larger than the capacity of the modified column, which will cause an error to be returned by your database system.
TIP:If you update a column(s) and notice an error after you run the update, issue the ROLLBACK command (as you would for an incorrect insert) to void the update.
The DELETE Statement
In addition to adding data to a database, you will also need to delete data from a database. The syntax for the DELETE statement is
DELETE FROM tablename WHERE condition
Depending on the use of the DELETE statements WHERE clause, SQL can do the following:
• Delete single rows
• Delete multiple rows
• Delete all rows
• Delete no rows
Here are several points to remember when using the DELETE statement:
• The DELETE statement cannot delete an individual field's values (use UPDATE instead). The DELETE statement deletes entire records from a single table.
• Like INSERT and UPDATE, deleting records from one table can cause referential integrity problems within other tables. Keep this potential problem area in mind when modifying data within a database.
• Using the DELETE statement deletes only records, not the table itself. Use the DROP TABLE statement to remove an entire table.
SQL> DELETE FROM COLLECTION WHERE WORTH <275
WARNING: Like the UPDATE statement, if you omit a WHERE clause from the DELETE statement, all rows in that particular table will be deleted.
WARNING:Always keep in mind that any modifications can affect the referential integrity of your database. Think through all your database editing steps to make sure that you have updated all tables correctly.