Update Existing Table Data

Let us see how we can perform update operation to make changes to existing data inside a table.

  • Typically we will have 2 clauses in update.

    • SET clause where the columns that are supposed to be updated.

    • Optional WHERE clause where we specify the condition to filter for rows that need to be updated.

  • As part of the WHERE clause generally we will have indexed column for performance reasons.

One need to have decent database and SQL skills to be comfortable with all types of application development. Feel free to Master SQL using Postgresql as target database using this course or playlist.

%run 05_function_get_database_connection.ipynb

%load_ext sql

%env DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db

%%sql 

SELECT user_id, user_role FROM users
WHERE user_id = 1

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

cursor = sms_connection.cursor()
query = ("""
    UPDATE users
        SET user_role = %s
    WHERE user_id = %s
""")

cursor.execute(query, ('A', 1))
sms_connection.commit()
sms_connection.close()

%%sql 

SELECT user_id, user_role FROM users
WHERE user_id = 1
Inserting Data into Table Deleting Data from Table