Updating records in SQL with the UPDATE statement

As database records need to stay up to date, you need to have a provision to update records from time to time to enforce changes. This includes updating existing records, archiving outdated data, and deleting irrelevant entries. For instance, consider a database table that stores customer information for an online store. Customer addresses or contact details may need to be regularly updated as users move or change their phone numbers.

To facilitate these changes, SQL provides the UPDATE query, which allows users to modify existing records in a table. The UPDATE statement can target specific rows based on defined criteria using the WHERE clause, ensuring that only the intended data is altered.

What is the SQL UPDATE statement?

The SQL UPDATE statement is a command used to modify existing records in a database table. It allows users to change the values of one or more columns for specific rows based on defined criteria. This capability is essential for maintaining accurate, up-to-date information within a database.

Syntax of the SQL UPDATE statement

The basic syntax for the SQL UPDATE command is as follows:

• table_name: The name of the table containing the records you want to update
• SET: Specifies which columns should be modified and assigns them new values
• column1 and column2: The names of the columns you wish to update
• value1 and value2: The new values that will replace the old ones in the specified columns
• WHERE condition: A clause that determines which rows will be affected by the update; if omitted, all rows in the table will be updated

Updating selected records in a single column

When you want to update records in a single column, you can use the UPDATE statement combined with a WHERE clause to specify which records should be modified. This ensures that only the intended rows are affected, preventing unsolicited changes to the entire dataset. Let's look at an example to understand the method.

Consider a table with the following structure:

Suppose Law has become one of your most profitable customers, and you need to update this record in the database. You would execute the following SQL command:

After executing this query, the table will look like this:

• Always include a WHERE clause when updating records; without it, all the rows in the specified column will be updated.
• Ensure that the new values conform to the data types and constraints defined for the column (e.g., NOT NULL constraints).

Updating multiple records in a single column

This allows you to modify the values of a specific column across multiple rows based on certain criteria. For example:

This part of the command indicates that you want to decrease the Average purchase value by 2,000 for all records that meet the specified condition.

The WHERE clause filters the records, ensuring that only the rows where the Customer type is Normal will be updated.

Updating selected records in multiple columns

This method allows you to modify more than one field in a table with a single query. This is particularly useful when you need to change related data across different columns for specific rows based on certain conditions. Let's look at the same example from before:

Suppose you want to update both the Average purchase value and Customer type for Nami, whose purchasing pattern has changed significantly in recent months.

This part of the command indicates that you want to change data in two columns:

• The Average purchase value will be updated to 23,000.
• The Customer type will be updated to Most profitable.

The WHERE clause filters the records, making sure that only the row where the Name is Nami will be updated.

Updating all records in a table's columns

The UPDATE query in SQL allows you to change the values of one or more columns for every row in a specified table. This can be useful when you need to apply a uniform change across all entries. Here's how you can do it:

Suppose you want to update the Customer type for all customers to Normal. Execute the following command:

All the records in the Customer type column will be updated to Normal.

The SQL statement cannot be executed if you attempt to change the Customer ID to 1 for all entries in the table. This is because, in most database designs, the Customer ID is a primary key, which means it must be unique for each record.

Why your business should go the extra mile to ensure database security

By default, most databases do not log sufficient activity data to facilitate thorough forensic investigations after a breach. When logging does occur, it often takes place within the same database, making it vulnerable to tampering by attackers who gain write access. This situation raises significant concerns regarding the reliability of database logs.

This is why robust database monitoring solutions are necessary to mitigate the inherent risks associated with databases, especially concerning data breaches and unauthorized access.

How ManageEngine EventLog Analyzer helps your business combat database security threats

ManageEngine EventLog Analyzer is a comprehensive log management solution that helps with database auditing by collecting, analyzing, correlating, searching for, and archiving logs from SQL servers, among others. This solution enables you to monitor databases effectively, identify operational issues, and detect unauthorized access to sensitive data in real time.

EventLog Analyzer tracks your business users' activities, enabling you to observe interactions with the database. This involves monitoring changes to specific columns and providing the details of who accessed that data and when, which is essential for maintaining the integrity of sensitive data.

With EventLog Analyzer's detailed auditing capabilities for SQL databases, administrators can keep track of all the processes being executed by users, including updates to database schemas and data manipulation actions (UPDATE, INSERT, and DELETE), as shown in Figure 1.

The solution is equipped with automated alerting capabilities that notify administrators immediately if suspicious activities are detected. For instance, if an unauthorized UPDATE query is executed a questionable number of times, the system triggers an alert based on predefined criteria (Fig. 2). This real-time response mechanism enables swift action to mitigate potential threats before they escalate.

EventLog Analyzer also includes mechanisms to detect common database attacks, such as SQL injection. By monitoring query patterns and flagging suspicious activities, it helps prevent unauthorized access and data exfiltration.