Update Statement

The UPDATE statement is used to update columns in a database table. We can create a visitor method to grab the UPDATE statement. In this chapter, we are going to write code to check if an UPDATE statement contains a WHERE clause. However, if the UPDATE statement is a temporary table or table variable, we won’t flag it as an error. The following code snippet can create a visitor method to grab UPDATE statements.

The node parameter will be an UpdateStatement type, and the key property will be the update specification object. If a clause is not found in the statement, the property for that clause will be null.

Listing 38 shows a sample stored procedure that updates tables.

Code Listing 38: Update statements

Target

The Target property is a NamedTableReference object, which can be used to determine the table being updated. The code snippet in Listing 39 shows how we can test for a missing WHERE clause, and that the table is not a temporary table.

Code Listing 39: Check target name if missing WHERE clause

The Schema.Object.BaseIdentifier provides the table name being updated. In addition, the server identifier, database identifier, and schema identifier can access each part of the table reference. Figure 12 illustrates a fully referenced table and properties to access each part.

[s11].[jbsite].[dbo].[Person]

Figure 12: Fully qualified table

FROM clause

The UPDATE statement allows you to update the data from another table, as shown in Listing 40.

Code Listing 40: Sample UPDATE FROM

If a FROM clause exists in the UPDATE statement, the FROM property will contain the table reference object. The FromClause object has a collection of TableReference objects, as shown in Table 23.

Table 23: From Clause properties

Set clauses

This collection is a list of AssignmentSetClause objects, an object for each column being updated. Due to a recent requested software update, the company would like all date columns to contain UTC (Coordinated Universal Time) dates, rather than local dates. SQL Server has a function called GetUTCDate(), which will return the UTC time.

We need code to review all UPDATE statements and report those statements that update a date field and use GetDate(), rather than GetUTCDate(). We do not want to report any updates to temporary tables, though.

Each item in the collection has the properties shown in Table 24.

Table 24: Set clause properties

Listing 41 shows code to check for the GetDate() function call, so it can be replaced with GetUtcDate().

Code Listing 41: Identify GetDate() updates

WHERE clause

The primary property on the WHERE clause is the search condition object, which returns the first and second expressions. For a simple WHERE (one condition), the object type is a Boolean comparison expression. In the WHERE filter (such as statecode = @statecode), the search condition object will contain a first expression (in this case, a FunctionCall object) and a second expression (a variable reference). The comparison type (equals) shows how the two expressions are compared. Table 25 shows the mapping between the code and the object.

Table 25: Example WHERE parsing

The object types will vary, such as column reference, string literal, and so on. The comparison type will be an enumeration, such as equals, greater than, or less than.

Example

An example UPDATE statement is shown in Figure 13.

Figure 13: Example UPDATE statement

Summary

The UPDATE statement has several properties and sources of the content to update. You can use this visitor method to search the columns being updated, determine which table is being updated, and possibly the table providing the update source.