Skip to main content

Update statement

In PostgreSQL, the UPDATE statement is used to modify or change existing records in a table. This is an essential function in the management and manipulation of data within databases. This tutorial will guide you through the concept, syntax, and usage of the UPDATE statement in SQL.

Understanding the UPDATE Syntax​

The basic syntax for the UPDATE statement is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
  • table_name: Specifies the table where the records will be updated.
  • SET: This clause specifies the column names and new values to replace the existing record(s).
  • column1, column2, ...: The columns in the table that you wish to update.
  • value1, value2, ...: The new values that you want to record in the database.
  • WHERE: This is an optional clause that specifies which record or records should be updated. If you omit the WHERE clause, all records in the table will be updated!

Basic Usage of UPDATE Statement​

Let's consider the following Students table:

IDNameAge
1Alice20
2Bob22
3Clara24

If we want to update Alice's age to 21, we would use the UPDATE statement as follows:

UPDATE Students
SET Age = 21
WHERE ID = 1;

After running this command, Alice's age in the Students table will be updated to 21.

Updating Multiple Columns​

To update more than one column at a time, simply separate column/value pairs with commas. For example, if we want to update Bob's name to 'Robert' and age to 23, we would do the following:

UPDATE Students
SET Name = 'Robert', Age = 23
WHERE ID = 2;

Updating All Rows​

If we omit the WHERE clause in the UPDATE statement, it results in updating all rows in the table. Let's say we made a mistake and all ages are off by one year, we can correct this by adding 1 to every student's age:

UPDATE Students
SET Age = Age + 1;

IMPORTANT: Be careful while using the UPDATE statement without a WHERE clause as it will update all records in the table.

Conclusion​

The UPDATE statement is a powerful SQL command used to change existing records in a database. It can be used to update one or more records and one or multiple columns at a time. Always remember to use the WHERE clause to specify the records that you want to update, otherwise, all records in the table will be updated.

Remember, practice is key when it comes to mastering SQL. So, keep practising these concepts with different examples to get a solid understanding of the UPDATE statement. Happy Learning!