Structured Query Language/DELETE 1
Hint: Be carefull and deactivate AUTOCOMMIT.
The DELETE command removes rows from a table.
DELETE FROM <tablename> WHERE <search_condition>;
The syntax is straightforward as we do not need to specify any column name - rows are deleted as a whole and not partly. As usual, the search condition specifies the criterion which identifies the affected rows. It can involve zero, one, or more rows. If we omit the WHERE keyword and the search condition all rows are affected.
Example[edit | edit source]
-- Delete one row DELETE FROM person WHERE lastname = 'Burton'; -- It's only a test. Restore the row. ROLLBACK;
The information about Mr. Burton was deleted and restored again.
Further Information[edit | edit source]
Exercises[edit | edit source]
Delete the hobby 'Yoga'.
-- Delete one row DELETE FROM hobby WHERE hobbyname = 'Yoga'; -- or: WHERE id = 6; ROLLBACK; -- if we want to restore the row COMMIT; -- if we want to commit our work -- Check the result SELECT * FROM hobby;
Delete all relations between persons and hobbies. Check result. Restore all rows.
-- compact syntax - great impact DELETE FROM person_hobby; -- Check the result SELECT * FROM person_hobby; -- restore everything ROLLBACK;