SA0037 : UPDATE statement without row limiting conditions |
![]() |
Absence of WHERE or JOIN clauses in UPDATE statements can unintentionally affect all rows in a table.

Performing an UPDATE operation without specifying a WHERE or JOIN clause can lead to modifying every record in the target table. This is often unintended and can cause data integrity issues.
— Example of problematic UPDATE query:
1UPDATE Employees SET Salary = Salary * 1.1;
In this example, the query increases the salary of all employees by 10%. Without a WHERE clause, the operation affects the entire table, which might not be the desired outcome, especially if only certain employees need a salary adjustment.
-
This approach can lead to massive data changes, requiring significant manual intervention to rectify mistakes.
-
Unintended data modifications may violate business rules or logic, impacting application functionality that relies on the database.

To resolve issues identified by SQL Enlight analysis rule `sa0037`, ensure your UPDATE statements include appropriate WHERE or JOIN clauses to prevent unintentional data modifications.
Follow these steps to address the issue:
-
Identify UPDATE statements lacking a WHERE or JOIN clause.
-
Determine the specific conditions or criteria that should limit the update to the intended rows. This might involve analyzing business logic or data requirements.
-
Modify the UPDATE statement to include a WHERE clause that specifies these conditions.
-
If necessary, incorporate a JOIN clause to consider related tables in determining the rows to update.
-
Test the revised UPDATE statement to ensure it only affects the target rows.
Example of corrected queries:
1-- Example of corrected UPDATE query with a WHERE clause 2UPDATE Employees 3SET Salary = Salary * 1.1 4WHERE Department = 'Sales'; 5 6-- Example of corrected UPDATE query with a JOIN clause 7UPDATE Employees 8SET Salary = e.Salary * 1.1 9FROM Employees e 10JOIN Departments d ON e.DepartmentId = d.Id 11WHERE d.Name = 'Sales';

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

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 |

The rule does not need Analysis Context or SQL Connection.


Design Rules, Bugs

There is no additional info for this rule.

SQL
1DECLARE @TempTable TABLE( Id int, Name nvarchar(100)) 2 3-- Temporary tables are ignored by the rule ( configurable by the IgnoreTempTargetTables rule parameter). 4UPDATE #TempTable SET Name='Test Name' 5 6-- Table variables are ignored by the rule. 7UPDATE @TempTable SET Name='Test Name' 8 9 10-- The UPDATE statement will affect ALL rows in table dbo.ProductsImport. 11-- This statement will cause analysis rule violation. 12UPDATE TestTable SET Name='Test Name' 13 14-- This UPDATE statement will be ignored by the rule as it has a filtering condition 15-- and `ConsiderJoinOnClausesAsFilter` is set to 'yes' 16UPDATE TestTable SET Name='Test Name' 17FROM TestTable 18INNER JOIN TestTable2 19ON TestTable.TestTable2Id=TestTable2.Id |

Message | Line | Column | |
---|---|---|---|
1 | SA0037 : UPDATE statement without row limiting conditions. | 12 | 0 |
2 | SA0037 : UPDATE statement without row limiting conditions. | 16 | 0 |
