#100DaysOfCode - Day 8: SQL DELETE + RSS Feeds!
As you may have learned, SQL is pretty powerful. Coming from a business background in school, I like to think of everything in terms of Excel tables, but there’s SO much that hasn’t been capable to do in Excel, that’s been around since SQL’s birth in the 70s. If the use of
JOINS really interested you, but the closest you’ll ever get to data processing is Excel, I highly recommend checking out Excel 2020’s xlookup function - it behaves very similarly to SQL
JOIN, and lets you separate complex spreadsheets into simplified tables that might make more sense!
Fortunately, we covered some of the most critical stuff for SQL data manipulation (besides getting a database setup) yesterday, but we didn’t actually get around to how to
DELETE things. While I won’t dive too much into the details of complex deletes, it’s actually fairly simple once you get the hang of
SELECT statements. I’m far from an expert, but I’ve been able to get by quite easily with deleting complex and sparse datasets just by being familiar with
SELECT * FROM <table>,
WHERE id=X, and
JOIN <table> (ON) value=value.
To show you how simple a delete is, here’s some of the more basic examples with
/* Delete all data from table A */ DELETE * FROM A /* Delete data from table A where the field ID has a value of 1 */ DELETE * FROM A WHERE A.ID = 1 /* Delete data from table A where the field ID starts with the character '1' (% is a wildcard and matches anything) */ DELETE * FROM A WHERE A.ID LIKE '1%'
WHERE statements, you could even get a bit more complex by nesting another
SELECT statement to evaluate the condition for types of rows you want to pull. For example, lets delete ALL rows in table A that aren’t referenced (or linked) by rows in table B:
DELETE * FROM A WHERE A.ID NOT IN (SELECT * FROM B.aID)
DELETE a bit more complex than
SELECT, but that’s because you can only delete data from one table at a time. Besides the additional complexity of needing to make multiple statements to delete from each table involved with a
JOIN, there’s another restriction that prevents you from delete data from tables with a primary key if (and only if) those values are referenced by another table as a foreign key (Note: if these links are done via application code only, this safety restriction does not apply)
So, let’s take the following tables, where a vehicle is linked to a color by ID:
Okay, so to explain what a FOREIGN KEY restriction is, I’ll show you.
In this case, lets say we want to delete a bunch of rows from
Colors (sorry I’m not being real creative here).
/* FAILS due to FOREIGN KEY CONSTRAINT on Red and Blue */ DELETE * FROM Colors
Well, in theory it would’ve worked… but, because since there are items in
Vehicles that are referencing these items, the table’s native restriction keeps us from deleting those rows in the
Colors table. In this case, we’ll want to delete the items from
Vehicles first, so that we can then delete the items from
So, lets delete all the records that are referencing
Colors as a Foreign Key:
/* This deletes the 3 items from `Vehicles` (pay particularly close attention to the character right after DELETE */ DELETE v FROM Colors c INNER JOIN Vehicles v ON c.ID = v.Color
/* This now succeeds! */ DELETE * FROM Colors
Overall? A bit more complex, but also similar to
To summarize, here are the differences:
- We can only delete from ONE table at a time
DELETEline, when using
JOINstatements, must specify the table it wants deleted via a table variable (e.g.
- We must delete FOREIGN KEYS before PRIMARY KEYS that the FK’s are referencing (unless this restriction is removed, which is bad practice)
Anyways, I hope this summary of
DELETE makes some sense to folks! Now, I’m off to try to improve some parts of this blog (such as the RSS feeds!)