Overview

CRUD? Create Read Update Delete – CRUD. This term refers to the four general actions that can be taken when it comes to data. This is a global term and does not just apply to databases (although it will be databases where you hear it the most). In SQL we use the INSERT command for creating data, SELECT to read, UPDATE to update and DELETE to delete.

Creating Data

When creating or inserting data into a table, there are a couple of ways to accomplish this depending on whether you are inserting a single row, multiple rows and what the source of the data is.  Here are some points that will help:

A Single Row with Specified Values – When inserting data into a single row where the values are known, we would generally use an INSERT/VALUE combination allowing you to specify the columns you are inserting into and the values you are inserting.

Insert from a Select – You can insert data into an existing table based upon the results of a select statement.  This can be useful if you need to populate a table with multiple rows based on one or more tables. To perform this we would use an INSERT/SELECT combination allowing you to specify the columns that you are inserting into and executing a select statement to get the values being inserted.

Select Into – Another option for inserting multiple rows into a table is to use a SELECT INTO statement.  This method allows you to perform a select command and, at the same time, insert the results directly into a table.  Although it sounds the same as the INSERT/SELECT method, it is actually very different.  The SELECT INTO command does not require the table to exist and will create a new table based on the data structure of the select statement.  This method is perfect for use with temporary tables but be very careful when using it to create permanent tables. To give you an example of a possible shortfall with this method: Imagine that your select statement returns a VARCHAR(200) based column and the longest entry in the select is 84 characters. The SELECT INTO will create you a column with a data type of VARCHAR(84).

Always Specify Column Names – It is possible to perform an insert into a table without entering column names or by using a number to represent the position of the column. All I will say is DON’T! If the structure of the table changes in any way, your statements may not work and you won’t know until it’s too late.

Reading Data

Reading data is achieved through the SELECT statement. The principle is that you specify what you would like to select, where from, any filters, groupings, conditions and what order you would like the data in. Some things that you may need to know when performing select statements:

Groups – When specifying data to be grouped, the columns that are being grouped must be contained within the column selection.  This also applies the other way round with the exception of when the column is contained within an aggregate function (e.g. MIN, SUM, etc…).

Conditions (HAVING) – You can only specify conditions using the HAVING statement if the data is being grouped.

ORDER BY – If the data is being grouped, the columns within the order by must be in the selected columns list (as with grouping).

Alias Column Names – When you are using an aggregate function or something other than a simple column selection, always ensure that you provide an alias for the column using the AS statement.

Updating Data

The UPDATE command is used to update existing data within a table.  The command can be used to update one or more rows in a single statement.  With this in mind it is important to ensure that your update statements are correct – the last thing you want to do is update data that should not have been updated. Here are some points to remember:

One Table at a Time – Remember that you can only update the data in a single table with each statement.

Update based on a Single Table – this is the simplest of updates where the statement is updating data within a table and the filters are filtering only data contained within that table.

Update While Filtering Data in other Tables – You can also perform more complex update commands where you update data from a select statement.  This is performed by using an UPDATE/FROM combination command.

Deleting Data

Deleting data using the DELETE statement is pretty straight forward. As with the UPDATE command, you must make sure that your delete statements are correct. Finding that a ‘not quite right’ delete statement has successfully cleaned out an entire table would not be acceptable to anyone.

Summary

That’s it for this post. Hopefully now you will have a brief understanding or basic CRUD statements. This only just covers the surface of ways to perform the myriad ways to manipulate data within SQL. I hope this post is helpful and as usual, constructive criticism and suggestions are welcome.