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.

%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.

%sql SELECT count(1) FROM users

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