Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
A while ago I mentioned that there is an acronym that you'll occasionally come across with SQL databases which was that of CRUD for create, read, update and delete. Create obviously being the INSERT INTO statement, read being our SELECT, update being UPDATE, and we might as well finish it off with some DELETE. DELETE has an associated word. Like we have UPDATE, SET and INSERT INTO and SELECT FROM, we have DELETE FROM and in fact DELETE is probably as close to SELECT as you're going to find.
In fact this is about the simplest statement there is. We say we're going to DELETE FROM a particular table. In this case I'm going to DELETE FROM SalesLT.ProductCategory and I want to be very careful here because if I hit F5 right now, I would go and delete every row in this table. The same way that if I said SELECT * FROM we bring back everything, this would delete everything, and certainly one of the dangers with using SQL Server management studio or having just admin level access to a database.
Yes, if you do the wrong thing, you can blow away a lot of stuff very easily. So all your DELETEs should have a WHERE. Unless you're wanting to delete the entire table you need some kind of WHERE clause here. Preferably you're going to be using something like a primary key, so in this case a ProductCategoryID and I know that I have one, 55 for example. And what I'm hoping as the when I execute this code I get little message that says one row affected.
Another thing to be very careful of is it's an easy thing to do something like highlight the DELETE statement and hit Execute but do bear in mind the SQL Server Management Studio if you highlight some code, it will actually execute just that code. So be careful what you have selected. You're going to hit Execute and we have one row affected. If I were to try that statement again, Execute, zero rows affected. Why? Well because the row with that ID has gone. It's just not there anymore.
And once again, if you're ever getting ready to do a DELETE statement and you're not sure how many things it's going to effect, there are a couple of ways of getting around that. But one of the simplest ways is just to say SELECT * FROM and copy your WHERE clause that you're just about to use. You could find out then now hopefully this will affect just this one row so I'll comment my SELECT, uncomment my DELETE< and give that go. One row affected, execute it again, zero rows affected. We're looking good.
The same options are available in your WHERE clause for a DELETE statement that are available for an UPDATE statement or a SELECT statement. It's the same thing.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64732 Viewers
80 Video lessons · 124333 Viewers
52 Video lessons · 60262 Viewers
59 Video lessons · 46099 Viewers