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
WHEREclause 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 NULLas condition to compare against Null values.
In [4]:
%load_ext sql
%env DATABASE_URL= postgresql://itversity_sms_user:itversity@pg.itversity.com/itversity_sms_db
The sql extension is already loaded. To reload it, use: %reload_ext sql env: DATABASE_URL=postgresql://itversity_sms_user:itversity@pg.itversity.com/itversity_sms_db
In [5]:
%sql SELECT user_id, user_password FROM users
6 rows affected.
Out[5]:
| user_id | user_password |
|---|---|
| 2 | None |
| 3 | None |
| 4 | h9LAz7p7ub |
| 5 | oEofndp |
| 6 | ih7Y69u56 |
| 1 | None |
In [6]:
%sql DELETE FROM users WHERE user_password IS NULL
* postgresql://itversity_sms_user:***@pg.itversity.com/itversity_sms_db 3 rows affected.
Out[6]:
[]
In [7]:
%sql SELECT user_id, user_password FROM users
* postgresql://itversity_sms_user:***@pg.itversity.com/itversity_sms_db 3 rows affected.
Out[7]:
| user_id | user_password |
|---|---|
| 4 | h9LAz7p7ub |
| 5 | oEofndp |
| 6 | ih7Y69u56 |
In [8]:
%sql SELECT count(1) FROM users
* postgresql://itversity_sms_user:***@pg.itversity.com/itversity_sms_db 1 rows affected.
Out[8]:
| count |
|---|
| 3 |