Click or drag to resize

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.

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
NameDescriptionDefault 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

RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0037

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

Example Test Script
 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

 MessageLineColumn
1SA0037 : UPDATE statement without row limiting conditions.220
2SA0037 : UPDATE statement without row limiting conditions.250
3SA0037 : UPDATE statement without row limiting conditions.510
See Also

Other Resources