Sai

Updating Data in PostgresQL

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.

In [1]:

%load_ext sql

In [2]:

%env DATABASE_URL=postgresql://itversity_sms_user:itversity@pg.itversity.com:5432/itversity_sms_db

env: DATABASE_URL=postgresql://itversity_sms_user:itversity@pg.itversity.com:5432/itversity_sms_db

Set user role for user_id 1 as ‘A’

In [3]:

%sql SELECT * FROM users

6 rows affected.

Out[3]:

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	2022-03-09 12:15:51.066866	2022-03-09 12:15:51.066866
2	Donald	Duck	donald@duck.com	False	None	U	False	2022-03-09 12:15:55.692717	2022-03-09 12:15:55.692717
3	Mickey	Mouse	mickey@mouse.com	False	None	U	True	2022-03-09 12:15:58.617705	2022-03-09 12:15:58.617705
4	Gordan	Bradock	gbradock0@barnesandnoble.com	False	h9LAz7p7ub	U	True	2022-03-09 12:16:02.576284	2022-03-09 12:16:02.576284
5	Tobe	Lyness	tlyness1@paginegialle.it	False	oEofndp	U	True	2022-03-09 12:16:02.576284	2022-03-09 12:16:02.576284
6	Addie	Mesias	amesias2@twitpic.com	False	ih7Y69u56	U	True	2022-03-09 12:16:02.576284	2022-03-09 12:16:02.576284

In [4]:

%%sql

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

 * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db
1 rows affected.

Out[4]:

[]

In [5]:

%sql SELECT * FROM users

 * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db
6 rows affected.

Out[5]:

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	2022-03-09 12:15:55.692717	2022-03-09 12:15:55.692717
3	Mickey	Mouse	mickey@mouse.com	False	None	U	True	2022-03-09 12:15:58.617705	2022-03-09 12:15:58.617705
4	Gordan	Bradock	gbradock0@barnesandnoble.com	False	h9LAz7p7ub	U	True	2022-03-09 12:16:02.576284	2022-03-09 12:16:02.576284
5	Tobe	Lyness	tlyness1@paginegialle.it	False	oEofndp	U	True	2022-03-09 12:16:02.576284	2022-03-09 12:16:02.576284
6	Addie	Mesias	amesias2@twitpic.com	False	ih7Y69u56	U	True	2022-03-09 12:16:02.576284	2022-03-09 12:16:02.576284
1	Scott	Tiger	scott@tiger.com	False	None	A	False	2022-03-09 12:15:51.066866	2022-03-09 12:15:51.066866

Set user_email_validated as well as is_active to true for all users

In [6]:

%sql SELECT user_id, user_email_validated, is_active FROM users

 * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db
6 rows affected.

Out[6]:

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

In [7]:

%%sql

UPDATE users
SET
    user_email_validated = true,
    is_active = true

 * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db
6 rows affected.

Out[7]:

[]

In [8]:

%sql SELECT user_id, user_email_validated, is_active FROM users

 * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db
6 rows affected.

Out[8]:

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

In [9]:

%sql SELECT user_id, user_email_id FROM users

 * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db
6 rows affected.

Out[9]:

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

In [10]:

%%sql

UPDATE users
SET
    user_email_id = upper(user_email_id)

 * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db
6 rows affected.

Out[10]:

[]

In [11]:

%sql SELECT user_id, user_email_id FROM users

 * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db
6 rows affected.

Out[11]:

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.

In [12]:

%%sql

ALTER TABLE users ADD COLUMN user_full_name VARCHAR(50)

 * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db
Done.

Out[12]:

[]

In [13]:

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

 * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db
6 rows affected.

Out[13]:

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

In [14]:

%sql SELECT concat(user_first_name, ' ', user_last_name) FROM users

 * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db
6 rows affected.

Out[14]:

concat
Donald Duck
Mickey Mouse
Gordan Bradock
Tobe Lyness
Addie Mesias
Scott Tiger

In [15]:

%%sql 

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

 * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db
6 rows affected.

Out[15]:

[]

In [16]:

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

 * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db
6 rows affected.

Out[16]:

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

Sai

Updating Data in PostgresQL

Updating Data¶

Share this post

Join Our Community

Follow Us

Links

Contact Info

Address

Phone

Email