Updating Data

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 update. One need to have good knowledge about 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.

  • Set user role for user_id 1 as ‘A’

%sql SELECT * FROM users

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
6 rows affected.
user_id user_first_name user_last_name user_email_id user_email_validated user_password user_role is_active create_ts last_updated_ts
1 Scott Tiger scott@tiger.com False None U False 2020-11-14 15:35:11.813351 2020-11-14 15:35:11.813351
2 Donald Duck donald@duck.com False None U False 2020-11-14 15:35:14.495991 2020-11-14 15:35:14.495991
3 Mickey Mouse mickey@mouse.com False None U True 2020-11-14 15:35:15.881686 2020-11-14 15:35:15.881686
4 Gordan Bradock gbradock0@barnesandnoble.com False h9LAz7p7ub U True 2020-11-14 15:35:20.938583 2020-11-14 15:35:20.938583
5 Tobe Lyness tlyness1@paginegialle.it False oEofndp U True 2020-11-14 15:35:20.938583 2020-11-14 15:35:20.938583
6 Addie Mesias amesias2@twitpic.com False ih7Y69u56 U True 2020-11-14 15:35:20.938583 2020-11-14 15:35:20.938583 
%%sql

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

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.

[]

%sql SELECT * FROM users

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
6 rows affected.
user_id user_first_name user_last_name user_email_id user_email_validated user_password user_role is_active create_ts last_updated_ts
2 Donald Duck donald@duck.com False None U False 2020-11-14 15:35:14.495991 2020-11-14 15:35:14.495991
3 Mickey Mouse mickey@mouse.com False None U True 2020-11-14 15:35:15.881686 2020-11-14 15:35:15.881686
4 Gordan Bradock gbradock0@barnesandnoble.com False h9LAz7p7ub U True 2020-11-14 15:35:20.938583 2020-11-14 15:35:20.938583
5 Tobe Lyness tlyness1@paginegialle.it False oEofndp U True 2020-11-14 15:35:20.938583 2020-11-14 15:35:20.938583
6 Addie Mesias amesias2@twitpic.com False ih7Y69u56 U True 2020-11-14 15:35:20.938583 2020-11-14 15:35:20.938583
1 Scott Tiger scott@tiger.com False None A False 2020-11-14 15:35:11.813351 2020-11-14 15:35:11.813351

  • Set user_email_validated as well as is_active to true for all users

%sql SELECT user_id, user_email_validated, is_active FROM users

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
6 rows affected.
user_id user_email_validated is_active
2 False False
3 False True
4 False True
5 False True
6 False True
1 False False 
%%sql

UPDATE users
SET
    user_email_validated = true,
    is_active = true

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
6 rows affected.

[]

%sql SELECT user_id, user_email_validated, is_active FROM users

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
6 rows affected.
user_id user_email_validated is_active
2 True True
3 True True
4 True True
5 True True
6 True True
1 True True

  • Convert case of user_email_id to upper for all the records

%sql SELECT user_id, user_email_id FROM users

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
6 rows affected.
user_id user_email_id
2 donald@duck.com
3 mickey@mouse.com
4 gbradock0@barnesandnoble.com
5 tlyness1@paginegialle.it
6 amesias2@twitpic.com
1 scott@tiger.com 
%%sql

UPDATE users
SET
    user_email_id = upper(user_email_id)

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
6 rows affected.

[]

%sql SELECT user_id, user_email_id FROM users

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
6 rows affected.
user_id user_email_id
2 DONALD@DUCK.COM
3 MICKEY@MOUSE.COM
4 GBRADOCK0@BARNESANDNOBLE.COM
5 TLYNESS1@PAGINEGIALLE.IT
6 AMESIAS2@TWITPIC.COM
1 SCOTT@TIGER.COM

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

%%sql

ALTER TABLE users ADD COLUMN user_full_name VARCHAR(50)

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.

[]

%sql SELECT user_id, user_first_name, user_last_name, user_full_name FROM users

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
6 rows affected.
user_id user_first_name user_last_name user_full_name
2 Donald Duck None
3 Mickey Mouse None
4 Gordan Bradock None
5 Tobe Lyness None
6 Addie Mesias None
1 Scott Tiger None 
%sql SELECT concat(user_first_name, ' ', user_last_name) FROM users

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
6 rows affected.
concat
Donald Duck
Mickey Mouse
Gordan Bradock
Tobe Lyness
Addie Mesias
Scott Tiger 
%%sql 

UPDATE users
    SET user_full_name = upper(concat(user_first_name, ' ', user_last_name))

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
6 rows affected.

[]

%sql SELECT user_id, user_first_name, user_last_name, user_full_name FROM users

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
6 rows affected.
user_id user_first_name user_last_name user_full_name
2 Donald Duck DONALD DUCK
3 Mickey Mouse MICKEY MOUSE
4 Gordan Bradock GORDAN BRADOCK
5 Tobe Lyness TOBE LYNESS
6 Addie Mesias ADDIE MESIAS
1 Scott Tiger SCOTT TIGER
Inserting Data Deleting Data