delete, truncate, and drop in SQL | Sololearn: Learn to code for FREE!
Новый курс! Каждый программист должен знать генеративный ИИ!
Попробуйте бесплатный урок
+ 11

delete, truncate, and drop in SQL

What is the difference between: delete truncate drop in SQL?

27th Apr 2017, 9:13 PM
Samuel Mayol
Samuel Mayol - avatar
5 ответов
+ 45
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. ex.: DELETE FROM emp WHERE employee = 'SAM'; TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE. ex.: TRUNCATE TABLE emp; The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back. ex.: DROP TABLE emp; DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
27th Apr 2017, 9:14 PM
Samuel Mayol
Samuel Mayol - avatar
+ 18
Along with Samuel's answer I wanted to point out that if your table is using a identity key (such as an auto incrementing id) that deleting a row or even all the data does not reset that key. For example, in a table of cars if the last entry had a primary identity key of 7 and then that row is DELETEd, the next INSERT would have a key of 8 not 7. TRUNCATE would in essence reseed the table back to 1. but as was pointed out it removes all the data in the table.
27th Apr 2017, 11:04 PM
+ 5
Here a link neatly explaining DELETE, INSERT, DDL, DML...
24th Aug 2018, 1:17 AM
JLP Maypa
JLP Maypa - avatar
+ 1
I really want to thank all
2nd Aug 2021, 10:59 PM
Maher Ahmed
Maher Ahmed - avatar
+ 1
So how do you undo the DELETE statement Samuel Mayol RobNichols
17th Jun 2022, 9:38 PM
Nwalozie Elijah
Nwalozie Elijah - avatar