{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "## Updating Data\n", "\n", "Let us see how we can update data in the table." ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "tags": [ "remove-cell" ] }, "outputs": [ { "data": { "text/html": [ "\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%%HTML\n", "" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* Typical syntax\n", "\n", "```sql\n", "UPDATE \n", "SET\n", " col1 = val1,\n", " col2 = val2\n", "WHERE \n", "```\n", "\n", "* If `WHERE` condition is not specified all rows in the table will be updated.\n", "* 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." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* Set user role for user_id 1 as 'A'" ] }, { "cell_type": "code", "execution_count": 26, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db\n", "6 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
user_iduser_first_nameuser_last_nameuser_email_iduser_email_validateduser_passworduser_roleis_activecreate_tslast_updated_ts
1ScottTigerscott@tiger.comFalseNoneUFalse2020-11-14 15:35:11.8133512020-11-14 15:35:11.813351
2DonaldDuckdonald@duck.comFalseNoneUFalse2020-11-14 15:35:14.4959912020-11-14 15:35:14.495991
3MickeyMousemickey@mouse.comFalseNoneUTrue2020-11-14 15:35:15.8816862020-11-14 15:35:15.881686
4GordanBradockgbradock0@barnesandnoble.comFalseh9LAz7p7ubUTrue2020-11-14 15:35:20.9385832020-11-14 15:35:20.938583
5TobeLynesstlyness1@paginegialle.itFalseoEofndpUTrue2020-11-14 15:35:20.9385832020-11-14 15:35:20.938583
6AddieMesiasamesias2@twitpic.comFalseih7Y69u56UTrue2020-11-14 15:35:20.9385832020-11-14 15:35:20.938583
" ], "text/plain": [ "[(1, 'Scott', 'Tiger', 'scott@tiger.com', False, None, 'U', False, datetime.datetime(2020, 11, 14, 15, 35, 11, 813351), datetime.datetime(2020, 11, 14, 15, 35, 11, 813351)),\n", " (2, 'Donald', 'Duck', 'donald@duck.com', False, None, 'U', False, datetime.datetime(2020, 11, 14, 15, 35, 14, 495991), datetime.datetime(2020, 11, 14, 15, 35, 14, 495991)),\n", " (3, 'Mickey', 'Mouse', 'mickey@mouse.com', False, None, 'U', True, datetime.datetime(2020, 11, 14, 15, 35, 15, 881686), datetime.datetime(2020, 11, 14, 15, 35, 15, 881686)),\n", " (4, 'Gordan', 'Bradock', 'gbradock0@barnesandnoble.com', False, 'h9LAz7p7ub', 'U', True, datetime.datetime(2020, 11, 14, 15, 35, 20, 938583), datetime.datetime(2020, 11, 14, 15, 35, 20, 938583)),\n", " (5, 'Tobe', 'Lyness', 'tlyness1@paginegialle.it', False, 'oEofndp', 'U', True, datetime.datetime(2020, 11, 14, 15, 35, 20, 938583), datetime.datetime(2020, 11, 14, 15, 35, 20, 938583)),\n", " (6, 'Addie', 'Mesias', 'amesias2@twitpic.com', False, 'ih7Y69u56', 'U', True, datetime.datetime(2020, 11, 14, 15, 35, 20, 938583), datetime.datetime(2020, 11, 14, 15, 35, 20, 938583))]" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT * FROM users" ] }, { "cell_type": "code", "execution_count": 27, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db\n", "1 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "UPDATE users \n", " SET user_role = 'A' \n", "WHERE user_id = 1" ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db\n", "6 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
user_iduser_first_nameuser_last_nameuser_email_iduser_email_validateduser_passworduser_roleis_activecreate_tslast_updated_ts
2DonaldDuckdonald@duck.comFalseNoneUFalse2020-11-14 15:35:14.4959912020-11-14 15:35:14.495991
3MickeyMousemickey@mouse.comFalseNoneUTrue2020-11-14 15:35:15.8816862020-11-14 15:35:15.881686
4GordanBradockgbradock0@barnesandnoble.comFalseh9LAz7p7ubUTrue2020-11-14 15:35:20.9385832020-11-14 15:35:20.938583
5TobeLynesstlyness1@paginegialle.itFalseoEofndpUTrue2020-11-14 15:35:20.9385832020-11-14 15:35:20.938583
6AddieMesiasamesias2@twitpic.comFalseih7Y69u56UTrue2020-11-14 15:35:20.9385832020-11-14 15:35:20.938583
1ScottTigerscott@tiger.comFalseNoneAFalse2020-11-14 15:35:11.8133512020-11-14 15:35:11.813351
" ], "text/plain": [ "[(2, 'Donald', 'Duck', 'donald@duck.com', False, None, 'U', False, datetime.datetime(2020, 11, 14, 15, 35, 14, 495991), datetime.datetime(2020, 11, 14, 15, 35, 14, 495991)),\n", " (3, 'Mickey', 'Mouse', 'mickey@mouse.com', False, None, 'U', True, datetime.datetime(2020, 11, 14, 15, 35, 15, 881686), datetime.datetime(2020, 11, 14, 15, 35, 15, 881686)),\n", " (4, 'Gordan', 'Bradock', 'gbradock0@barnesandnoble.com', False, 'h9LAz7p7ub', 'U', True, datetime.datetime(2020, 11, 14, 15, 35, 20, 938583), datetime.datetime(2020, 11, 14, 15, 35, 20, 938583)),\n", " (5, 'Tobe', 'Lyness', 'tlyness1@paginegialle.it', False, 'oEofndp', 'U', True, datetime.datetime(2020, 11, 14, 15, 35, 20, 938583), datetime.datetime(2020, 11, 14, 15, 35, 20, 938583)),\n", " (6, 'Addie', 'Mesias', 'amesias2@twitpic.com', False, 'ih7Y69u56', 'U', True, datetime.datetime(2020, 11, 14, 15, 35, 20, 938583), datetime.datetime(2020, 11, 14, 15, 35, 20, 938583)),\n", " (1, 'Scott', 'Tiger', 'scott@tiger.com', False, None, 'A', False, datetime.datetime(2020, 11, 14, 15, 35, 11, 813351), datetime.datetime(2020, 11, 14, 15, 35, 11, 813351))]" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT * FROM users" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* Set user_email_validated as well as is_active to true for all users" ] }, { "cell_type": "code", "execution_count": 29, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db\n", "6 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
user_iduser_email_validatedis_active
2FalseFalse
3FalseTrue
4FalseTrue
5FalseTrue
6FalseTrue
1FalseFalse
" ], "text/plain": [ "[(2, False, False),\n", " (3, False, True),\n", " (4, False, True),\n", " (5, False, True),\n", " (6, False, True),\n", " (1, False, False)]" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT user_id, user_email_validated, is_active FROM users" ] }, { "cell_type": "code", "execution_count": 30, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db\n", "6 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "UPDATE users\n", "SET\n", " user_email_validated = true,\n", " is_active = true" ] }, { "cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db\n", "6 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
user_iduser_email_validatedis_active
2TrueTrue
3TrueTrue
4TrueTrue
5TrueTrue
6TrueTrue
1TrueTrue
" ], "text/plain": [ "[(2, True, True),\n", " (3, True, True),\n", " (4, True, True),\n", " (5, True, True),\n", " (6, True, True),\n", " (1, True, True)]" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT user_id, user_email_validated, is_active FROM users" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* Convert case of user_email_id to upper for all the records" ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db\n", "6 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
user_iduser_email_id
2donald@duck.com
3mickey@mouse.com
4gbradock0@barnesandnoble.com
5tlyness1@paginegialle.it
6amesias2@twitpic.com
1scott@tiger.com
" ], "text/plain": [ "[(2, 'donald@duck.com'),\n", " (3, 'mickey@mouse.com'),\n", " (4, 'gbradock0@barnesandnoble.com'),\n", " (5, 'tlyness1@paginegialle.it'),\n", " (6, 'amesias2@twitpic.com'),\n", " (1, 'scott@tiger.com')]" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT user_id, user_email_id FROM users" ] }, { "cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db\n", "6 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "UPDATE users\n", "SET\n", " user_email_id = upper(user_email_id)" ] }, { "cell_type": "code", "execution_count": 34, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db\n", "6 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
user_iduser_email_id
2DONALD@DUCK.COM
3MICKEY@MOUSE.COM
4GBRADOCK0@BARNESANDNOBLE.COM
5TLYNESS1@PAGINEGIALLE.IT
6AMESIAS2@TWITPIC.COM
1SCOTT@TIGER.COM
" ], "text/plain": [ "[(2, 'DONALD@DUCK.COM'),\n", " (3, 'MICKEY@MOUSE.COM'),\n", " (4, 'GBRADOCK0@BARNESANDNOBLE.COM'),\n", " (5, 'TLYNESS1@PAGINEGIALLE.IT'),\n", " (6, 'AMESIAS2@TWITPIC.COM'),\n", " (1, 'SCOTT@TIGER.COM')]" ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT user_id, user_email_id FROM users" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* Add new column by name **user_full_name** and update it by concatenating **user_first_name** and **user_last_name**." ] }, { "cell_type": "code", "execution_count": 35, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db\n", "Done.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "ALTER TABLE users ADD COLUMN user_full_name VARCHAR(50)" ] }, { "cell_type": "code", "execution_count": 36, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db\n", "6 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
user_iduser_first_nameuser_last_nameuser_full_name
2DonaldDuckNone
3MickeyMouseNone
4GordanBradockNone
5TobeLynessNone
6AddieMesiasNone
1ScottTigerNone
" ], "text/plain": [ "[(2, 'Donald', 'Duck', None),\n", " (3, 'Mickey', 'Mouse', None),\n", " (4, 'Gordan', 'Bradock', None),\n", " (5, 'Tobe', 'Lyness', None),\n", " (6, 'Addie', 'Mesias', None),\n", " (1, 'Scott', 'Tiger', None)]" ] }, "execution_count": 36, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT user_id, user_first_name, user_last_name, user_full_name FROM users" ] }, { "cell_type": "code", "execution_count": 37, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db\n", "6 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
concat
Donald Duck
Mickey Mouse
Gordan Bradock
Tobe Lyness
Addie Mesias
Scott Tiger
" ], "text/plain": [ "[('Donald Duck',),\n", " ('Mickey Mouse',),\n", " ('Gordan Bradock',),\n", " ('Tobe Lyness',),\n", " ('Addie Mesias',),\n", " ('Scott Tiger',)]" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT concat(user_first_name, ' ', user_last_name) FROM users" ] }, { "cell_type": "code", "execution_count": 38, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db\n", "6 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 38, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "UPDATE users\n", " SET user_full_name = upper(concat(user_first_name, ' ', user_last_name))" ] }, { "cell_type": "code", "execution_count": 39, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db\n", "6 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
user_iduser_first_nameuser_last_nameuser_full_name
2DonaldDuckDONALD DUCK
3MickeyMouseMICKEY MOUSE
4GordanBradockGORDAN BRADOCK
5TobeLynessTOBE LYNESS
6AddieMesiasADDIE MESIAS
1ScottTigerSCOTT TIGER
" ], "text/plain": [ "[(2, 'Donald', 'Duck', 'DONALD DUCK'),\n", " (3, 'Mickey', 'Mouse', 'MICKEY MOUSE'),\n", " (4, 'Gordan', 'Bradock', 'GORDAN BRADOCK'),\n", " (5, 'Tobe', 'Lyness', 'TOBE LYNESS'),\n", " (6, 'Addie', 'Mesias', 'ADDIE MESIAS'),\n", " (1, 'Scott', 'Tiger', 'SCOTT TIGER')]" ] }, "execution_count": 39, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT user_id, user_first_name, user_last_name, user_full_name FROM users" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.12" } }, "nbformat": 4, "nbformat_minor": 4 }