SA0037 : UPDATE statement without row limiting conditions

The topic describes the SA0037 analysis rule.

Message

UPDATE statement without row limiting conditions

Description

The rule looks for UPDATE statements not having WHERE and JOIN clauses.

How to fix

Consider reviewing your code to avoid unintentionally updating all the records in the table.

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters
Name Description Default Value
IgnoreTempTargetTables

Ignore targets that are temporary tables or table variables.

yes
ConsiderJoinOnClausesAsFilter

The parameter specifies if the existence of JOIN clauses to be considered as row filtering criteria.

no
IgnoreFiltredCteTargetTables

Ignore target tables which are common table expressions and have filtering clause.

yes
Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
20 minutes per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1DECLARE @TempTable TABLE( Id int,
 2                          Name nvarchar(100))
 3
 4-- Temporary tables are ignored by the rule ( configurable by the IgnoreTempTargetTables rule parameter).
 5UPDATE     #TempTable SET Name='Test Name'
 6
 7UPDATE     #TempTable SET Name='Test Name'
 8WHERE      Id=13
 9
10UPDATE     TestTable SET Name='Test Name'
11WHERE      Id=13
12
13-- Table variables are ignored by the rule.
14UPDATE     @TempTable SET Name='Test Name'
15
16UPDATE     @TempTable SET Name='Test Name'
17WHERE      Id=13
18
19
20-- The UPDATE statement will affect ALL rows in table dbo.ProductsImport.
21-- This statement will cause analysis rule violation.
22UPDATE     TestTable SET Name='Test Name'
23
24-- This UPDATE statement will be ignored by the rule as it has a filtering condition.
25UPDATE     TestTable SET Name='Test Name'
26FROM       TestTable
27INNER JOIN TestTable2
28ON         TestTable.TestTable2Id=TestTable2.Id
29
30UPDATE     TestTable SET Name='Test Name'
31FROM       TestTable /*IGNORE:SA0037*/
32
33UPDATE     TestTable SET Name='Test Name'
34FROM       #TestTable TestTable
35INNER JOIN TestTable2
36ON         TestTable.TestTable2Id=TestTable2.Id
37
38;WITH 
39SQLTEMP1  
40        AS( SELECT mp_program 
41            FROM mp_latest_rev_tmp 
42            LEFT OUTER JOIN SQLTEMP 
43            ON  eoae_ac_reg_no  = mp_aircraft 
44            WHERE mp_guid    = 1 
45            AND  eohdr_ouinstance IS NULL),
46SQLTEMP AS( SELECT mp_program FROM mp_latest_rev_tmp ) 
47UPDATE SQLTEMP1  set mp_program  = 1
48
49;WITH 
50SQLTEMP1 AS( SELECT mp_program FROM mp_latest_rev_tmp )
51UPDATE SQLTEMP1  set mp_program  = 1
Analysis Results
  Message Line Column
1 SA0037 : UPDATE statement without row limiting conditions. 22 0
2 SA0037 : UPDATE statement without row limiting conditions. 25 0
3 SA0037 : UPDATE statement without row limiting conditions. 51 0
See Also

Other Resources

Analysis Rules