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
WHEREcondition 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
WHEREclause to take care of complex conditions. UsingWHEREwill 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 |