Manipulating Information in the Database Using ColdFusion MX

You can use various operations to manipulate data in databases. One of these operations uses the <CFQUERY> tag and SQL statements, such as INSERT. Another method is to use ColdFusion tags, such as <CFINSERT>, without SQL statements. These tags were discussed in Chapter 4, "Form Handling Using ColdFusion MX," in conjunction with the use of ColdFusion forms.

Some typical data manipulation functions are

• Inserting data into a database

• Updating data in a database

• Deleting data from a database

• Updating multiple tables

Inserting Data into a Database

You can use the SQL INSERT statement to write information to a database. A new row is added to the database in a write operation. The following is the syntax of an INSERT statement:

 INSERT INTO table_name(column_names) VALUES(value_list) 

Here, column_names specifies a comma-separated list of columns and value_list specifies a comma-separated list of values. The order of the values is important; it has to correspond to the order specified for column names in the table.

The following SQL statement adds a new row to the employeedata table:

 INSERT INTO employeedata(EmpID, LName, Fname,Salary) VALUES(123, 'Trueman', 'Max',2000) 

This statement creates a new row in employeedata and sets the values of the EmpID, LName, FName, and Salary fields of the row.

The LName and FName values in the query are placed within single quotes. This is necessary because the columns are defined to contain character strings. Numeric values don't require quotes.

The remaining fields in the row are set to Null, which means that a field doesn't contain a value. During the creation of a table, Null value properties can be set on the table and its columns. If a field supports Null values, you can omit it from the INSERT statement. The database sets the field to Null automatically when a new row is inserted. If the field doesn't support Null values, you need to specify a value for it as part of the INSERT statement. If this value is absent, the database returns an error.

To add multiple records retrieved from a table to an existing table, use the SELECT clause of the INSERT statement. Begin the clause with the SELECT keyword followed by the names of the columns that you want to retrieve from the table. The order and the number of columns should match what's specified in the table. The syntax for INSERT INTO is

 INSERT INTO table_name (column1_name, column1_name, column1_name) SELECT (integer_value, 'text_value, 'memo_value') FROM table_name 

CFINSERT can also insert records without using SQL statements. This was discussed in Chapter 4, in conjunction with form usage.

Updating Data in a Database

The UPDATE statement modifies the values of a table row. This statement allows you to update the fields of either a specific row or all rows in the table. The syntax for UPDATE is

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

The UPDATE statement uses the WHERE clause to specify the rows that you want to modify. Each field to be updated is specified using the SET clause. A comma separates multiple fields. The WHERE clause specifies the record you want to update.

The following statement updates the leave status of Max Trueman:

 UPDATE employeedata SET Leave_status=43 WHERE EmpID = 123 

Omit the WHERE clause and execute the following statement to update the Leave_status field for all rows in the table:

 UPDATE employees SET Leave_status = 43 

The <CFUPDATE> tag also updates records without using the SQL statements. <CFUPDATE> was discussed in Chapter 4.

Deleting Data from a Database

The DELETE statement removes entire rows from a table. You can use this statement to delete either a single row or multiple rows. Once a record has been deleted, you lose that data permanently. The syntax for DELETE is

 DELETE FROM table_name [WHERE search_condition] 

You can specify a WHERE clause with the DELETE statement to delete specific rows of the table. The records from the specified table are selected on the basis of the primary key. If a record matches the primary key, it's selected and deleted. For example, the following statement deletes Max Trueman from the table:

 DELETE FROM employeedata WHERE EmpID=123 

The DELETE statement can also delete multiple records from the table. You can select and delete a group of records having one or more common values. For example, to delete the records of all employees who have leave status equal to 40, use the following statement:

 DELETE FROM Employeedata WHERE Leave_Status=40 

Use the following statement to remove all the rows from employeedata:

 DELETE FROM employeedata