Database Essentials using Postgres - Updating Data in Postgres Database Table

Let us see how we can update data in the table.

  • Typical syntax
UPDATE <table_name>
SET
    col1 = val1,
    col2 = val2
WHERE <condition>
  • If WHERE condition is not specified, all rows in the table will be updated.

  • For now, we will see basic examples for an update. One needs to have good knowledge about the WHERE clause to take care of complex conditions. Using WHERE will be covered extensively as part of filtering the data at a later point in time.

Key Concepts Explanation

Set user role for user_id 1 as ‘A’

UPDATE users 
SET user_role = 'A' 
WHERE user_id = 1

Set user_email_validated as well as is_active to true for all users

UPDATE users
SET
    user_email_validated = true,
    is_active = true

Convert case of user_email_id to upper for all the records

UPDATE users
SET
    user_email_id = upper(user_email_id)

Add new column by name user_full_name and update it by concatenating user_first_name and user_last_name

ALTER TABLE users ADD COLUMN user_full_name VARCHAR(50)
UPDATE users
SET user_full_name = upper(concat(user_first_name, ' ', user_last_name))

Hands-On Tasks

Perform the following tasks to practice updating data:

  1. Set user role for user_id 1 as ‘A’
  2. Set user_email_validated as well as is_active to true for all users
  3. Convert the case of user_email_id to upper for all the records
  4. Add a new column by the name ‘user_full_name’ and update it by concatenating ‘user_first_name’ and ‘user_last_name’.

Conclusion

In this article, we discussed how to update data in a table using SQL commands. It is essential to understand the syntax and functionality of the UPDATE statement to modify existing data efficiently. Practice these tasks to strengthen your SQL skills and feel free to engage with the community for further learning opportunities.

Watch the video tutorial here