Eventually, you may find the need to modify existing database records. Whether it is to correct an error or update old information, you need to use a SQL UPDATE statement inside of a CFQUERY tag. In the UPDATE statement, you must tell ColdFusion which table and fields to modify.
In section, you will use the Cosby Speakers Bureau example to learn how to modify exisling speaker data in the speakers table.
The first part of the UPDATE statement is where you specify the table that contains the record you want to modify. This is a required part of the UPDATE statement. You begin the UPDATE statement with the UPDATE keyword followed by the name of the table. The format is UPDATE tab 1e_name.
The next part of your UPDATE statement requires you to define the values for the fields you are updating. You begin this clause with the SET keyword followed by the field naAtes you are updating equal to the new field values. You can update as many fields as you wish. Just make sure that your values are the correct data type for each field that you are updating. The format for the SET clause is UPDATE.
table_name SET fieldl-valuel, field2=’value2′, field3 ••’value3′.
This is all that is required for an UPDATE statement to be operational. However, if you use an UPDATE statement without a WHEREclause, you will update every single record in the table. In most cases, this is not what is intended. Therefore, you should add a WHERE clause to your UPDATE statement.
Adding a WHEREclause to an UPDATE query is just like adding a WHERE~lause to a SELECT query. First, add the WHEREkeyword followed by one or n;tore expressions. The expressions must evaluate to TRUE or FALSE and be separated by AND or OR operators. The example in Listing 10.10 updates the record that was inserted in Listing 10.9:
Updating a record in the speakers table
Deleting Database Records
In order to delete database records, you need to use a SQL DELETE statement inside of a CFQUERY tag’. In the DELETE statement, you must tell Coldfusion which table you want to .delete records from as well as which record should be deleted. However, you must use this statement with caution. Deleting records is irreversible and it cannot be undone.
In the following section, you will delete the specific record that you’ previously inserted and updated in the speakers table.
The first part of the DELETE statement is where you specify the table that contains the record you want to delete. This is a required part of the DELETE statement. You begin the DELETE statement with the DELETE FROMkeywords followed by the name of the table
The format is DELETE FROMtab 1e_nar,le_This is a completely valid SQL statement, but it will delete all records contained in the table specified. To specify which records should be deleted, you must add a WHERE clause.
The WHERE clause in a DELETE statement filters which records will be deleted from a database table. Adding a WHERE clause to a DELETE query is just like adding a WHERE clause to a SELECT or UPDATE query. First, add the WHERE keyword followed by one or more expressions. The expressions must evaluate to TRUE or FALSE and be separated by AND or ORoperators. The example in Listing 10.11 deletes the record that was updated in Listing 10.10.
Once a DELETEquery has been executed, it cannot be undone. It is therefore wise to test your query first. You can do this by making the DELETE,statement a SELECTstatement instead. To convert the query in Listing 10.11 to a SELECTstatement you would remove the DELETEFROMkeywords and replace them with the SELECT”FROMclause. The code would be written as follows:
If the record returned in the results is the one you want to delete, then you can use the DELETEquery. You can use this technique with INSERT,UPDATEa,nd DELETEqueries.
SQLExpressions, Operators, and Functions
In order to get the most out of your queries, you need to be able to effectively and efficiently filter your query results. You do this by adding one or more conditions to your query WHEREclause. Conditions are comprised of column names, values, and operators that compare the two. You have already seen some simple SQL conditions in the section “Retrieving Records from a Database.” They most often take the following format:
WHERE column_name operator value
column_nameis most often the name of the field that you are evaluating. The operator tells the database how to compare the two values (co l umn riame and val ue). The va 1ue is used to evaluate the column_name. The value can be hard coded, or you can even pass variable data to it in the form of a ColdFusion variable:
WHERE speaker.speaker_id – #speaker_id#
In this scenario, you are evaluating whether the table field speaker id is equal to the ColdFusion variable speaker id using the operator. By passing ColdFusion variables to your SQL statements, you are creating dynamic queries. Dynamic queries are discussed in the last section of this chapter.
A SQL operator is a reserved word or character used in a SQL statement. It is primarily used in the WHERE clause to do comparisons or arithmetic operations, although it can be used in other parts of your SQL statement. The four types of SQL operators that will be covered in this chapter are comparison, conditional, arithmetic, and logical. While this section does go into great depth about working with SQL, it is not comprehensive. If you would like to learn more advanced interactions ooing SQL; refer to Chapter 11, “Using Advanced Query Techniques,” or check out the SQL book recommendations in the Introduction of this book.
Comparison operators are used to determine if the co 1umn_nameon the left side of the operand meets the criteria defined by the value on the right side. The result returned is TRUE, FALSE, or UNKNOWN. UNKNOWN is returned if SQL finds a NULL value (see the explanation of NULL in the Conditional Operators section). UNKNOWN is often converted to FALSE by mast versions of SQL. There are six comparison operators.
The equal operator (=) has been used several times throughout this chapter. It is probably the most commonly used and most useful SQL operator. It often results in limiting your recordset more than any other operator does when comparing a primary key to a single value. You can use this operator with both strings and numerical values. The following example returns only one record:
The not-equal operator (c> is the exact opposite of the equal operator. It excludes all records that match its criteria. Again, you can use it with beth strings and numerical values. Depending on your version of SQL, this operator can take any of the following formats: <>, !~, “=. The following example returns all records except one:
The less than operator.allows you to retrieve all records where the value to the left of the operand is less than the value to the right of the operand. You can compare both numbers and characters. If you query for all numbers less than 5, your results would be 1,2, 3, and 4. If you query for all letters less than d, your results would be, and c. Uppercase may be evaluated before lowercase characters depending on your version of SQL.
In the following query, all speakers with a last name in the first half of the alphabet up until’m’ will be returned:
Less Than or Equal To
The less than or equal to operator is similar to the less than operator except that it includes the value to the right of the operand. You can compare both numbers and characters. If you query for all numbers less than or equal to 5, your results would be I, 2, 3, 4, and S.li you query for all letters less than or equal to d, your results would be a, b. c. and d. Uppercase may be evaluated before lowercase characters depending on your version of SQL. In Listing 10.12, all speakers with a last name in the first half of the alphabet up urttil and indud~g ‘m’ will be returned. The results can be seen in Figure 10.12
Finding Fields Less Than a Particular Value
The greater than operator allows you to retrieve all records where the value to the left of the operand is greater than the value to the right of the operand, You can compare both numbers and characters. If you query for all numbers greater than 5, your results wouldbe 6, 7, 8, 9 and so on. If you query for all letters greater than x, your results would be y and z. Uppercase may be evaluated before lowercase characters depending on your ver sion of SQL. In the following query, all speakers with a last name in the second half of the alphabet after ‘n’ will be returned:
Greater Tt,an or Equal To
The greater than or equal to operator is similar to the greater than operator except that it includes the value to the right of the operand. You can compare both numbers and characters. If you query fOTall numbers greater than or equal to 5, your results would be 5,6, 7, 8, and so on. If you query for a11letters greater than or equal to x, your results wouid be x, y, and z. Uppercase may be evaluated before lowercase characters depending on your version of SQL. In Listing 10.13, all speakers with a last name in second half of the alphabet starting with n will be returned.
Finding Fields Greater Than a Particular Value
Conditional operators are used to determine if the column nameon the left side of the operand fulfills a particular condition. There are four basic conditional operators, and they are described.
IS [NOT) NULL
IS NULLsearches for fields that have no value associated with them. NULLis different than an empty string, blank, or 0; these are actual values. NULLactually means that no value or data-whatsoever is associated “lith the field. If NOTis included in the operator, then the opposite is true. IS NOTNULL searches for fields that have some value associated with them. including empty strings, blanks, or O’s.Listing 10.14and Figure 1O. 1-b demonstrate using IS NULL and IS NOT NULL.
If you are trying to determine whether a field value falls within the inclusive range of two values, y.ou would use the BETWEEN operator. The following format should be used: co1umn_name BETWEEN val uel AND val ue2. BETWEEN can be used with both numerical values and characters. Listing 10.15 and Figure 10.15 demonstrate using BETNEEN. The code in Listing ~0.15 produces the exact same results as the code.
If you are trying to determine whether a field value is included in a list of values or in a subquery, you should use the IN operator. The following format should be used: column_name IN (subquery_or _1; st). Strings and numbers can be evaluated in either the list or the subquery results. If you are using strings, then each list item needs to be surrounded by single quotes. If you are using a ColdFusion variable or a form variable as the list and it contains strings instead of numbers, then you may need to use the PreserveS; ng1eQuotesO function.
If your list contains numbers, then you should not use any quotes. Listing 10.16 and Figure 10.16 demonstrate using IN.
The CFQUERY Tag and SQL 201 The LIKE operator is used to match the value to the left of the operand to a string pattern combined with wildcards on the right. LIKE is case sensitive, It takes the format col umn_name LIKE I text_pattern I, You can use the three following symbols that act as wild cards in the value to the right of the LiKE operand:
• % (Percent sign)-Use the percent sign at the beginning or ending of the value to specify that the beginning or ending of a string is variable.
• (Underscore sign)-Use the underscore in any location that you want to specify a single wildcard character.
•  (Brackets)-Use brackets to provide specific matching criteria for a single character.
Look at Table 10.8 to better grasp how each wildcard can be used. An example can also be seen in Listing 10.17, with the results.