SQL: Delete & Drop with Billy Idol memes
Introduction
Hello fellow reader. Thanks for clicking on this blog! Today I’ll be talking about deleting from our database with SQL statements. If you’re not familiar with SQL statements, please check out these other blogs I wrote around this topic. They should give you more context about SQL, such as syntax.
Cool Blog about SQL😎 ...SQL Select- https://ineedmoreplates.medium.com/sql-select-tables-ninja-turtles-d655adc9d5a8SQL Create - https://ineedmoreplates.medium.com/create-sql-tables-panda-memes-8f10973a3859SQL Insert - https://ineedmoreplates.medium.com/sql-insert-into-a-table-parrot-memes-a355232e81b1SQL Update - https://ineedmoreplates.medium.com/updating-tables-sql-starwars-memes-%EF%B8%8F-%EF%B8%8F-576717920268Couldn't hurt right? 🤕
Let’s dive in 🐋
Imagine we’ve massed a large amount of data about our beloved William Michael Albert Broad! Yet something doesn’t seem quite right. It looks like a fan has entered in completely inaccurate information about Billy. We need to jump on this before it becomes a problem. We decide it’s best to delete the entry from our database. How are we going to do this?
Luckily, Delete works very similarly to how “UPDATE” works in SQL. Notice how we use the DELETE + FROM + WHERE combination. It’s important to remember that we have targeted a specific row we’d like to delete in this particular case.
🚨 Please remember, just like UPDATE if you don’t include the WHERE clause you’ll delete ALL THE DATA from that table, and Billy will be very mad at you! So watch out!!! 🚨 Let’s take a peek at the syntax…
our codeDELETE FROM billy_idol_collection WHERE id = 6 <--😝 lucky number 6!--------------------
syntaxDELETE FROM table_name WHERE condition;
Cool 😎 ! DELETE is a statement you’ll often find yourself implement in your own applications. However, you may not always want to target a specific row. What about batch deleting rows? Let’s take a look at deleting multiple rows by referencing columns.
our codeDELETE FROM billy_idol_collection WHERE id IN(6, 7, 12) --------------------------
syntaxDELETE FROM table_name WHERE column_name IN(row1, row2, row3,..);
Notice the difference? We modify our WHERE clause here. The column name targets the general location inside the table, and inside the IN operator, we select which rows we’d like to delete based on that column. We selected the primary key, in this case, so we plug in integers that represent the ids.
Nice 👍! But how do I clear a table? Easy! Yet before we do, let’s be sure we actually want to delete all the data. Typically when building and testing, folks will do this. Yet, in a professional setting, it’s widely considered a bad idea.
🚨 It's a good practice to drop tables from the database in general. Think carefully about what your doing, and how it will effect the project. When building applications you'll use a ton of "dummy data" for testing, but that shouldn't translate into forming a bad habit. Please keep that in mind 🚨 try this statement…
our codeDROP TABLE billy_idol_collection; <-- all gone, even the table 🚨 --------------------------
syntaxDROP TABLE table_name;
Unless you need to refactor the way you’ve modeled your data. I think a more appropriate solution to scrapping your table clean would be to Truncate.
our codeTRUNCATE billy_idol_collection; <-- clean & empty table 🧼 --------------------------
syntaxTRUNCATE TABLE table_name;
Conclusion
Thanks for taking the time to read this blog. I hope you’ve learned a bit more about DELETE SQL statements. Check out the links below. They dive deeper into DELETE. Now write some code, play around with the concept, testing and playing are how you’ll best absorb this concept. Stay tuned for more 📺 and happy coding 🤠 !
Links
SQL DELETE - https://www.w3schools.com/sql/sql_delete.aspSQL TRUNCATE vs DELETE - https://stackoverflow.com/questions/139630/whats-the-difference-between-truncate-and-delete-in-sqlDelete from two tables - https://stackoverflow.com/questions/1233451/delete-from-two-tables-in-one-query
