Deleting Data

Let us understand how to delete the data from a table.

  • Typical Syntax - DELETE FROM <table> WHERE <condition>.

  • If we do not specify condition, it will delete all the data from the table.

  • It is not recommended to use delete with out where condition to delete all the data (instead we should use TRUNCATE).

  • For now we will see basic examples for delete. One need to have good knowledge about WHERE clause to take care of complex conditions.

  • Let’s see how we can delete all those records from users where the password is not set. We need to use IS NULL as condition to compare against Null values.

%sql SELECT user_id, user_password FROM users

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
6 rows affected.
user_id user_password
2 None
3 None
4 h9LAz7p7ub
5 oEofndp
6 ih7Y69u56
1 None 
%sql DELETE FROM users WHERE user_password IS NULL

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

[]

%sql SELECT user_id, user_password FROM users

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
3 rows affected.
user_id user_password
4 h9LAz7p7ub
5 oEofndp
6 ih7Y69u56 
%sql SELECT count(1) FROM users

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.
count
3
Updating Data Overview of Transactions