Blog

Using the UPDATE Statement

The UPDATE command statement in SQL database management systems is used to modify or update existing table records in a database table. The UPDATE command helps database users manually modify existing table row and column data and information in a database table, including the ability to edit or customize one or more table data and information.

Syntax of the UPDATE command statement.

The basic syntax of the UPDATE command statement in SQL databases is.

UPDATE table_name

SET column1 = value1, column2 = value2, …

WHERE condition;

Elements of the UPDATE command statement.

• table_name – This is the name of the database table that contains all the table records that the database user wants to update or modify in the table.
• SET column1 = value1, column2 = value2, …: – This indicates all the table columns in the database that need to be updated by the database user, along with the new values ​​that the user wants to modify in the existing table columns.
• WHERE condition – In the WHERE condition, the database user sets certain conditions that indicate which specific row and column in the database table should be updated or modified. This ensures that there is no negative impact on the columns of the existing database table.

Examples of the UPDATE command statement in SQL databases.

Updating a single column in an SQL database.

To update the value of a single column in a particular row in a database table, use the UPDATE command statement with the SET clause and the WHERE condition.

Example of updating a single column.

UPDATE employee

SET emp_age = 25

WHERE employee_id = 101;

In this example.

• Here, in this example, the emp_age column is updated to 25 for the employee with employee_id = 101. Here, the WHERE clause or condition ensures that the update is applied only to the table row where employe_id = 101, preventing any unnecessary modifications to other rows in the employee table.

Updating Multiple Columns in a Database Table.

Database users can update multiple table columns simultaneously in an UPDATE command statement by separating multiple table column-value combinations with a comma operator.

Example of Updating Multiple Table Columns.

UPDATE employe

SET emp_age = 39, department = ‘Development’

WHERE employe_id = 201;

In this example.

• Here, for the employee with employe_id = 201, both the emp_age and department columns are updated simultaneously.
• The new emp_age and department ‘Development’ are set based on the WHERE condition.

Updating Multiple Rows in a Database Table.

Database users can update multiple table rows by creating a particular condition that matches more than one row in the existing database table.

Example of Updating Multiple Rows.

UPDATE employe

SET department = ‘Ui Designer’

WHERE emp_age > 35;

In this example.

• Here, the department is updated to ‘Ui Designer’ for all employees whose age is greater than 35 years.
• Remember, since the WHERE condition matches multiple employees, multiple table rows can be affected.

Updating All Rows in a Database Table.

If the database user ignores the WHERE command clause, all rows of the employee table can be updated simultaneously. Therefore, be very careful when applying this, as it can cause many unwanted updates in your table.

Example of Updating All Rows.

UPDATE employe

SET department = ‘Creator’;

In this example.

• In this example, the `department` column in the `employee` table will be updated to ‘Creator’ for all employees.
• Since no WHERE clause is applied, these modifications are applied to every row in the `employee` table.

Using expressions in the UPDATE statement in a database table.

Database users can also use logical expressions or calculations to update the values ​​of a table column. For example, a user can increase an employee’s salary by a particular percentage.

Example of an UPDATE statement with an expression.

UPDATE employee

SET salary = salary * 1.5

WHERE department = ‘Marketing’;

In this example.

• Here, the `salary` column is updated by increasing it by 10% (salary * 1.5) for all employees in the Marketing department.
• The WHERE clause ensures that these modifications only affect employees in the Marketing department.

Updating Database Tables with Subqueries.

Database users can apply subqueries within an UPDATE statement to update table columns from the results of another query.

Example of Updating Database Tables with Subqueries.

UPDATE employe

SET department = (SELECT department FROM departments WHERE department_id = 102)

WHERE employe_id = 107;

In this example.

• for employee_id = 107, department is updated to the department returned by the subquery.
• Similarly, the subquery extracts the department name from the departments table where department_id = 102.

Important points about the WHERE clause.

• Using the WHERE clause – Remember to always apply the WHERE clause to the command to specify the rows in the database table you want to update. If you don’t do this, all rows in the existing table will be updated simultaneously.
• Backup before updating – Before applying an UPDATE operation to a large or important dataset, it’s always a good practice to back up your database table or use a transaction to ensure the integrity of the database data.
• Transaction Control – If your database system supports data transactions, such as MySQL, PostgreSQL, or SQL Server, you can use database commands or tools like BEGIN TRANSACTION, COMMIT, and ROLLBACK to ensure that updates to existing database tables are applied in the proper order and can be easily rolled back in the event of an unexpected error.

Example of using transactions with UPDATE in a database (optional).

Using transactions in database tables.

If a database user is applying multiple UPDATE operations to a database table and wants to ensure that all these table updates or modifications succeed or fail simultaneously, the database user can apply a transaction.

BEGIN TRANSACTION;

UPDATE employe

SET department = ‘Design’

WHERE department = ‘Development’;

UPDATE employe

SET emp_age = emp_age + 1

WHERE department = ‘Design’;

COMMIT;

In this example.

• two updates are made within a single transaction.
• The COMMIT statement ensures that both table updates are applied simultaneously. If a mistake occurs, the user can ROLLBACK the transaction to reverse both modifications.

Rolling back a transaction in a database table.

If a database user makes a mistake while applying an UPDATE operation and needs to reverse all modifications, you can apply the ROLLBACK statement.

BEGIN TRANSACTION;

UPDATE employe

SET department = ‘Design’

WHERE department = ‘Development’;

— Here, assume a mistake has been made.

ROLLBACK;

In this case.

• The ROLLBACK command statement in this example will reverse any modifications made by the preceding UPDATE statement query.

Explanation of sql UPDATE Statement

Conclusion of the UPDATE Statement in SQL Database Management Systems.

• The UPDATE command statement in SQL database management systems is a powerful feature for updating or modifying existing table records in a database.
• The UPDATE statement allows a database user to update one or multiple table columns.
• The database can update or modify one or more tables daily based on some particular condition in the table.
• It can apply calculations or subqueries to dynamically update table data.