Update SQL: how to update data in SQL

Update in SQL is an operation that modifies existing information in database tables . This allows you to update, create or delete records according to usage needs , ensuring the timeliness and reliability of the data.

In this article, we will explore the basic concepts of Update and present its syntax for different uses. In addition, we will discuss the types of Updates, such as single and multiple Updates, and present techniques and best practices to optimize queries and ensure effectiveness and efficiency in your SQL operations with Update.

Update syntax in SQL

The command syntax  UPDATE in SQL is used to update records in a table or dataset. Command has following basic structure:

UPDATE [table_name]
SET [column1] = [value1], [column2] = [value2], ...
WHERE [condition];

The command elements  UPDATE are:

1. UPDATE: It is part of the command that indicates that we want to update information in a table.
2. [table_name] : This is the name of the table we want to update. This way, if we are updating a table without specifying a name, we can leave this space blank.
3. SET: We use this command to define the columns that we want to update, along with their respective values.
4. [column1] = [value1], [column2] = [value2], … : Here we define the columns we want to update, along with the new values ​​we want to assign to those columns. Values ​​can be expressed as a string, a constant, a variable, or the result of a function.
5. WHERE: We use this command to specify a condition that must be true for the update to occur. This way, if we are going to update all the records in the table, we can leave this space blank.
6. [condition] : This is the condition that must be true for the update to occur. The condition can be a combination of columns and operators (e.g. ‘=’ for equality, ‘<>’ for inequality, ‘>’, ‘<‘, ‘>=’, ‘<=’ for comparisons, etc.).

Here is an example command  UPDATE that updates a student’s name in a table called “students”:

UPDATE students
SET name = 'New Name'
WHERE student_id = 1;

In this example, we are updating the “name” column of the student with the ID “1” to the value “New Name”. The condition  WHERE student_id = 1 guarantees that only the student with ID 1 will be updated. This way, If we are updating all records in the table, we can leave the condition blank.

Update types in SQL

There are different types of Updates in SQL, each with its own purpose and use. Here are some of the common update types:

Single and multiple updates

• Simple update : We use simple update to update one or more values ​​in a table. This way, we use the statement UPDATE followed by the name of the table and the fields we want to update, and then specifying the updated value for each field.

In the example, we are updating the first name and last name of all customers in the “clients” table to “John” and “Doe”, respectively. Additionally, we are only updating the record with customer ID 1. See:

UPDATE clients
SET first_name = 'John', last_name = 'Doe'
WHERE client_id = 1;

• Multiple update : We use multiple update to update multiple values ​​in a table at the same time. It is done by using the statement UPDATE followed by the name of the table and the fields you want to update, and then specifying the updated value for each field.

In this example below, we are updating the name, address and city of all customers in the “customers” table with customer ID between 1 and 5.

UPDATE customers
SET name = 'John Doe', address = '123 Main St', city = 'Anytown'
WHERE customer_id BETWEEN 1 AND 5;

Update affiliates and independents

We use the affiliate and independent update to update data for a specific group of affiliates or independents. This way, we use the statement UPDATE followed by the name of the table and the fields we want to update, and then specifying the updated value for each field, and then using code WHERE to specify which records we want to update.

In the example, we are updating the commission of all affiliates in the “affiliates” table with ID between 1 and 5 to 20%. Look:

UPDATE affiliates
SET commission = 20
WHERE affiliate_id BETWEEN 1 AND 5;

Block and graph update

• Block update : We use block update to update a specific block of cells in a table. This way, we use the statement UPDATE followed by the name of the table and the fields we want to update, and then specifying the updated value for each field and the code WHERE to specify which block we want to update.

This example updates the  name,  price and  inventory record columns with  id 1 in the table  products. Look:

UPDATE products
SET name = 'New Product Name', price = 19.99, inventory = 100
WHERE id = 1;

• Graph update : We use graph update to update a data graph. So we use the statement UPDATE followed by the name of the table and the fields we want to update, and then specifying the updated value for each field and the code WHERE to specify which records we want to update.

In the example we update the columns  name,  price and  inventory for three different records in the table  products, using code WHERE to specify the records that should be updated:

UPDATE products
SET name = 'New Product Name', price = 19.99, inventory = 100
WHERE id = 1;

UPDATE products
SET name = 'New Product Name', price = 19.99, inventory = 100
WHERE id = 2;

UPDATE products
SET name = 'New Product Name', price = 19.99, inventory = 100
WHERE id = 3;

Techniques and best practices for Update SQL

UPDATE is an SQL statement, we use it to update records in a table. Therefore, there are several techniques and best practices for using UPDATE SQL, which aim to ensure efficiency, consistency and data security. Here are some of them:

1. Use the codeWHERE : We use it WHERE to specify which records will be updated. Thus, when using the code WHERE, the number of affected records is minimized, which can improve query performance.
2. Update multiple fields at once: When we need to update multiple fields in a table, do one UPDATEwith a single statement rather than executing multiple UPDATEseparate statements.
3. Use prepared queries: Prepared queries are a way to prevent SQL injection attacks. This way, they can also improve query performance, as the SQL IDE can optimize the query once and reuse the execution sheet.
4. Update only necessary columns: Avoid updating columns that do not need Update. Thus, helping to reduce query execution time and minimize the amount of data transferred.
5. Use the ORDER BY and LIMIT code: When we need to update just some records or records in a specific order, use the code ORDER BYand LIMITin conjunction with the code WHERE.
6. Test and evaluate query performance: Test different combinations of code and parameters to find the best performance for your update. Consider using indexes and SQL engine optimization to further improve performance.

Advanced Update SQL features

SQL offers several features for managing concurrent access and updating of data in a database. Some of these features include:

Update with transactions

We can do the Update as part of a transaction. Thus, ensuring that all updates are consistent and that the database is in a consistent state after we complete the transaction.

BEGIN TRANSACTION;
UPDATE customers SET name = 'John Doe' WHERE id = 1;
COMMIT;

In this example, updates are made as part of a transaction BEGIN TRANSACTION and COMMIT. Therefore, by performing the command as shown, Update ensures that the database is consistent after the transaction is complete.

Locks

We use locks to protect data integrity and ensure that only one transaction can access and update a given set of data at a time.

SELECT * FROM customers WHERE id = 1 FOR UPDATE;
UPDATE customers SET name = 'John Doe' WHERE id = 1;

In this sense, in the example we use the SELECT with clause FOR UPDATE to block access to the record with id = 1, ensuring that only one transaction can access and update this data set at a time.

Sessions for Concurrent Access Management

We apply sessions to the codes to ensure that a transaction has exclusive access to the data during its execution, preventing other transactions from accessing or modifying the data during this time.

BEGIN TRANSACTION;
DECLARE @customer_id INT = 1;
UPDATE customers SET name = 'John Doe' WHERE id = @customer_id;
COMMIT;

Therefore, we do not use a session to ensure that the transaction has exclusive access to the data during its execution, preventing other transactions from accessing or modifying the data during this time.

Update using stored procedures and functions

We use it to encapsulate the data update logic and ensure that the execution of the Update command is consistent and safe. This ensures data integrity and protects the database against errors.

CREATE PROCEDURE UpdateCustomer
@customer_id INT,
@name NVARCHAR(50)
AS
BEGIN
UPDATE customers SET name = @name WHERE id = @customer_id;
END;

EXEC UpdateCustomer @customer_id = 1, @name = 'John Doe';

And finally, in the last example shown we used a stored procedure to encapsulate the data update logic. This way, This ensures consistent and safe execution of the Update command and ensuring data and database integrity against errors.

Examples and practical use of Update SQL

Here are some practical examples of  UPDATE SQL statements, using different features and techniques:

• Example 1: Update all records in a table

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    column3 = value3;

• Example 2: Update records with a specific ID

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    column3 = value3
WHERE id = specific_id;

• Example 3: Update multiple records with a single instruction

UPDATE table_name
SET column1 = value1,
    column2 = value2
WHERE condition1;

UPDATE table_name
SET column3 = value3
WHERE condition2;

• Example 4: Using queries prepared in SQL Server

DECLARE @id INT = 1;
DECLARE @column1 INT = 2;
DECLARE @column2 INT = 3;

UPDATE table_name
SET column1 = column1 + @column1,
    column2 = column2 + @column2
WHERE id = @id;

• Example 5: Update only some records with LIMIT

UPDATE table_name
SET column1 = value1
WHERE condition1
ORDER BY id
LIMIT 5;

• Example 6: Using the clause  ORDER BY with  UPDATE and DELETE

UPDATE table_name
SET column1 = value1
WHERE condition1
ORDER BY id
DELETE FROM table_name
WHERE condition2;

• Example 7: Update multiple related tables

UPDATE table_name1
SET column1 = value1
WHERE condition1;

UPDATE table_name2
SET column2 = value2
WHERE table_name1.foreign_key = specific_id;

• Example 8: Using triggers to ensure consistency

CREATE TRIGGER trg_after_update_table1
ON table_name1
AFTER UPDATE
AS
BEGIN
  UPDATE table_name2
  SET column2 = updated_value
  WHERE table_name1.foreign_key = specific_id;
END;

• Example 9: Update output files as needed

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    column3 = value3
WHERE condition1
AND NOT EXISTS (SELECT 1 FROM output_table WHERE output_column = value1);

• Example 10: Update only records that go through a procedure

UPDATE table_name
SET column1 = value1
WHERE condition1
AND EXISTS (SELECT 1 FROM another_table WHERE another_column = table_name.foreign_key);

Therefore, these examples demonstrate different situations in which we use  UPDATE including multiple record updates, condition-based updates, and updates related to other tables.