SQL tutorial 35: DELETE and TRUNCATE how to delete data from a table

Key Takeaways

what's up internet I'm Manish from rebellionrider.com and today's SQL tutorial is all about SQL delete and truncated statement till so far we have seen three different ways of inserting data into a table so I thought to wrap the whole how to insert data series with this tutorial and today in this tutorial we will learn how to delete or remove data from a table using delete and truncate a statement once again I'll be using the student table which I have used so far in this how to insert data into a table series and fortunately this student table has few rows in it and we are going to delete them let's start today's tutorial with delete DML command delete command can be used to delete entire as well as selected rows from a table or views let's take a look at the syntax as it's a delete query thus it starts with delete keyword followed by from keyword after that you have to specify the table name from which you want to delete the data and at the end we have our wear Clause wear Clause enables you to choose rows from the reference table or view that need to be deleted conditionally that is the only those rows that meet the search condition are deleted if you omit the wear CLA all the rows in the table or view are deleted let's perform our first query say we want to delete the row where S2 ID is four student with S2 ID 4 is Batman and we will delete this row let's write the cury we are deleting selected row does we have used where CLA here with is2 ID for delete condition as we just want to delete only those records where is 2 ID is 4 let's execute one draw deleted now refresh here as you can see the record for student Batman which has is2 id4 is now deleted similarly now say you want to delete all the rows from a table as we are deleting all the rows from a table thus we do not need to specify the wear Clause let's execute now let's refresh here now we don't have any rows in our table as you can see here delete is a DML command and all the data which has been deleted using SQL delete command get stored into a buffer till you roll back or commit means if you have deleted something from your table accidentally and you want it back then you can perform roll back to get back your data to perform roll back you can either write rollback and execute or if you are using SQL Developer then press function key F12 or press this roll back button let's do it as we have already deleted all the rows from our table and now we do not have any rows here let's perform a roll back let's execute now let's refresh here and here is our data now let's take a look at the truncate command unlike delete truncate is a ddl command which can be used to delete all the rows from a table since we Clause is not allowed with truncate command thus we cannot delete selected rows using trunade let's do a query say you want to delete all the rows again from a student table as you can see here we have four rows in a student table now let's write the cury let me make some space so that you can see what I'm going to do next I think everything is now perfectly visible truncate command has pretty similar syntax you just need to write truncate table both are the keywords followed by the name of table from which you want to delete the data before executing this query mind that we cannot perform a roll back once we truncate means roll back is not possible with truncate command and the deletion is permanent let's execute table student truncated now let's refresh here once again all the rows are deleted let's try to roll back now let's execute roll back completed now let's try to refresh nothing happens let's try to roll back by pressing this roll back button now let's again refresh nothing happens for more in-depth knowledge of the topic as well as the difference between truncate and delete command you can read my blog link is in the description box hope you enjoyed watching hit the thumbs up and keep me motivated moreover you can comment your feedback and don't forget to subscribe that's it for today's guys we will'll see you soon this is Manish from Rebellion Rider