#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!

SQL Delete

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 SELECT and WHERE

/* 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%'

With 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)

Moving on, JOINS make 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:

Colors

ID	Color
1	Red
2	Blue
3	Green

Vehicles

ID	Type	Color
1	Van	1
2	Truck	2
3	Bus	2

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 Colors.

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 SELECT.

To summarize, here are the differences:

1. We can only delete from ONE table at a time
2. The DELETE line, when using JOIN statements, must specify the table it wants deleted via a table variable (e.g. c for Colors).
3. 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!)

~ Moxnr