You use SQL INSERT statement to write information to a database. A write adds a new row to a database table. The basic syntax of an INSERT statement is as follows:

INSERT INTO table_name(column_names) VALUES(value_list)

where:

• column_names specifies a comma-separated list of columns.

• value_list specifies a comma-separated list of values. The order of values has to correspond to the order that you specified column names.

For example, the following SQL statement adds a new row to the employees table:

INSERT INTO employees(EmpID, LastName, Firstname) VALUES(51, 'Smith', 'John')

This statement creates a row in the employees table and sets the values of the EmpID, LastName, and FirstName fields of the row. The remaining fields in the row are set to Null. Nullmeans that the field does not contain a value.

When you, or your database administrator, creates a table, you can set properties on the table and the columns of the table. One of the properties you can set for a column is whether the field supports Null values. If a field supports Nulls, you can omit the field from the INSERT statement. The database automatically sets the field to Null when you insert a new row.

However, if the field does not support Nulls, specify a value for the field as part of the INSERT statement; otherwise, the database issues an error.

The LastName and FirstName values in the query are contained within single-quotation marks. This is necessary because the table columns are defined to contain character strings. Numeric data does not require the quotation marks.

Use the UPDATE statement in SQL to update the values of a table row. Update lets you update the fields of a specific row or all rows in the table. The UPDATE statement has the following syntax:

UPDATE table_name  
    SET column_name1=value1, ... , column_nameN=valueN  
    [ WHERE search_condition ] 

Do not attempt to update a record’s primary key field. Your database typically enforces this restriction.

The UPDATE statement uses the optional WHERE clause, much like the SELECT statement, to determine which table rows to modify. The following UPDATE statement updates the e-mail address of John Smith:

UPDATE employees SET Email='jsmith@mycompany.com' WHERE EmpID = 51

Be careful using UPDATE. If you omit the WHERE clause to execute the following statement:

UPDATE employees SET Email = 'jsmith@mycompany.com' 

you update the Email field for all rows in the table.

The DELETE statement removes rows from a table. The DELETE statement has the following syntax:

DELETE FROM table_name 
    [ WHERE search_condition ] 

You can remove all rows from a table using a statement in the form:

DELETE FROM employees

Typically, you specify a WHERE clause to the DELETE statement to delete specific rows of the table. For example, the following statement deletes John Smith from the table:

DELETE FROM employees WHERE EmpID=51

The preceding examples describe how to modify a single database table. However, you might have a database that uses multiple tables to represent information.

One way to update multiple tables is to use one INSERT statement per table and to wrap all INSERT statements within a database transaction. A transaction contains one or more SQL statements that can be rolled back or committed as a unit. If any single statement in the transaction fails, you can roll back the entire transaction, canceling any previous writes that occurred within the transaction. You can use the same technique for selects, updates, and deletes. The following example uses the cftransaction tag to wrap multiple SQL statements:

<cftransaction> 
 
<cfquery name="qInsEmp" datasource="cfdocexamples"> 
    INSERT INTO Employees (FirstName,LastName,EMail,Phone,Department) 
    VALUES ('Simon', 'Horwith', 'SHORWITH','(202)-797-6570','Research and Development') 
</cfquery> 
 
<cfquery name="qGetID" datasource="cfdocexamples"> 
    SELECT MAX(Emp_ID) AS New_Employee 
    FROM Employees 
</cfquery> 
 
</cftransaction>