Use SQL Like a Badass – Part 2/4

Now that we know how to select data, we will start mucking around with the data within a table. You may want to add or delete a column, update the data within a column based off some logic or insert more data into the table. These can be achieved via the ALTER TABLE, UPDATE and INSERT commands.

But first, a quick Chuck Norris fact.

 

2. ALTER TABLE

The most useful aspect of ALTER TABLE is to add or drop columns. To add a column, you would use the below syntax.

In our Chuck Norris example, let’s add a new column ‘comment_range’ as a flag for whether comments are High, Medium or Low.

This gives us a a blank column which we can modify. Note that if you want to modify the data type of a column, you can use the below expression. Note that common data types are VARCHAR(n) – of length n e.g. VARCHAR(50) , FLOAT and INTEGER. For more data types, see Data Types.

Sometimes, we have too much junk in a table, so me way want to delete some columns. We can do this via DROP COLUMN.

3. UPDATE

UPDATE changes existing records in a table. Often when you create a new column, you then want to update it with data based off a set of criteria. The simple syntax to use for an UPDATE statement is:

A useful way to update data within a table is to use the CASE command. This is very similar to an IF statement in excel. In SQL the syntax of a case statement is When…Then…Else…In our Chuck Norris example, we may want to label comments < 5 as Low, between 5 and 10 Medium and 10 and above as High. This can be execute using the following:

This gives us the new new column ‘comment_range’, with the new values of L,M or H.

idquotecomment_qtycomment_range
1When Chuck Norris does a pushup, he isn't lifting himself up, he's pushing the Earth down.4L
2Chuck Norris' facial hair is known to cut diamonds.3L
3Chuck Norris once robbed a gun store with a knife.2L
4Chuck Norris abducts aliens.15H
5Chuck Norris once fought superman. The loser had to wear his underwear over his pants.7M
6Chuck Norris can make a happy meal cry.13H
7Chuck Norris counted to infinity. Twice.8M
8In space Chuck Norris can hear you scream.0L
9Chuck Norris is the reason Waldo is hiding.11H
10M.C. Hammer learned the hard way that Chuck Norris can touch this.3L

4. INSERT

Say you have a few rows you want to add into the data, you would use INSERT INTO followed by the values you want to add.

In our Chuck Norris example, if we have one new quote we want to add on, we could use the statement below

In real world cases we have large data sets and we do not want to append data manually in a query. Often we have 2 tables that we want to combine. We can use INSERT and SELECT if the table format is identical. Note that more complex data blending requires joins, which we will come to shortly.

 

Well done on learning how to Update and Alter tables. Next time we will pick up on joining data sets together with Use SQL Like a Badass – Part 3/4.

Share on LinkedInEmail this to someoneTweet about this on TwitterShare on FacebookPrint this page

Leave a Reply

Your email address will not be published. Required fields are marked *

*