Home

Description

The DELETE and UPDATE statements should contain a WHERE clause in order to limit the scope of the operation. Without a WHERE clause, the statement will delete or update all rows in a table, which is not usually the desired outcome. The WHERE clause should specify the criteria for the rows to be affected by the statement. This ensures that only the intended rows are affected and that other rows are not unintentionally modified.

Key Benefits

• Safety: DELETE and UPDATE statements should contain a WHERE clause to ensure that only the intended rows are affected.
• Precision: DELETE and UPDATE statements should contain a WHERE clause to ensure that only the intended rows are modified.
• Consistency: DELETE and UPDATE statements should contain a WHERE clause to ensure that the same rows are affected each time the statement is executed.

Non-compliant Code Example

PROCEDURE UpdateCustomeCity(customerId in integer, city in nvarchar2(200))
IS
BEGIN
UPDATE CUSTOMERS        --Non compliant code (DELETE OR UPDATE statements without WHERE clause)
	SET City=city;
	
END UpdateCustomeCity;

Compliant Code Example

PROCEDURE UpdateCustomeCity(customerId in integer, city in nvarchar2(200))
IS
BEGIN
UPDATE CUSTOMERS        --Compliant code (DELETE OR UPDATE statements with WHERE clause)
	SET City=city
    WHERE Id = customerId;;
	
END UpdateCustomeCity;