Title: Drop A Column
Slug: drop_a_column
Summary: Delete a column in a table in SQL.
Date: 2016-05-01 12:00
Category: SQL
Tags: Basics
Authors: Chris Albon

Note: This tutorial was written using Catherine Devlin's SQL in Jupyter Notebooks library. If you have not using a Jupyter Notebook, you can ignore the two lines of code below and any line containing %%sql. Furthermore, this tutorial uses SQLite's flavor of SQL, your version might have some differences in syntax.

For more, check out Learning SQL by Alan Beaulieu.



In [1]:

    
# Ignore
%load_ext sql
%sql sqlite://
%config SqlMagic.feedback = False

Create Data



In [2]:

    
%%sql

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Stacy Miller', 23, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (621, 'Betty Bob', NULL, 'F', 'Petaluma', 1);
INSERT INTO criminals VALUES (162, 'Jaden Ado', 49, 'M', NULL, 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (411, 'Bob Iton', NULL, 'M', 'San Francisco', 0);









    Out[2]:





[]

View Table



In [3]:

    
%%sql

-- Select everything
SELECT *

-- From the table 'criminals'
FROM criminals









    Out[3]:





    
        pid
        name
        age
        sex
        city
        minor
    
    
        412
        James Smith
        15
        M
        Santa Rosa
        1
    
    
        234
        Bill James
        22
        M
        Santa Rosa
        0
    
    
        632
        Stacy Miller
        23
        F
        Santa Rosa
        0
    
    
        621
        Betty Bob
        None
        F
        Petaluma
        1
    
    
        162
        Jaden Ado
        49
        M
        None
        0
    
    
        901
        Gordon Ado
        32
        F
        Santa Rosa
        0
    
    
        512
        Bill Byson
        21
        M
        Santa Rosa
        0
    
    
        411
        Bob Iton
        None
        M
        San Francisco
        0

Delete Column (Most Common)

%%sql -- Alter the table called 'criminals' ALTER TABLE criminals -- From the table 'criminals' DROP COLUMN age

Delete Column (SQLite)

SQLite (the version of SQL used in this tutorial) does not allow you to drop a column. The workaround is to make a new table that contains only the columns you want to keep, then rename the new table to the original template's name.



In [4]:

    
%%sql

-- Create a table called 'criminals_tamps' with the columns we want to not drop
CREATE TABLE criminals_temp(pid, name, sex);

-- Copy the data from the columns we want to keep to the new table
INSERT INTO criminals_temp SELECT pid, name, sex FROM criminals;

-- Delete the original table
DROP TABLE criminals;

-- Rename the new table to the original table's name
ALTER TABLE criminals_temp RENAME TO criminals;









    Out[4]:





[]

View Table



In [5]:

    
%%sql

-- Select everything
SELECT *

-- From the table 'criminals'
FROM criminals









    Out[5]:





    
        pid
        name
        sex
    
    
        412
        James Smith
        M
    
    
        234
        Bill James
        M
    
    
        632
        Stacy Miller
        F
    
    
        621
        Betty Bob
        F
    
    
        162
        Jaden Ado
        M
    
    
        901
        Gordon Ado
        F
    
    
        512
        Bill Byson
        M
    
    
        411
        Bob Iton
        M

pid	name	age	sex	city	minor
412	James Smith	15	M	Santa Rosa	1
234	Bill James	22	M	Santa Rosa	0
632	Stacy Miller	23	F	Santa Rosa	0
621	Betty Bob	None	F	Petaluma	1
162	Jaden Ado	49	M	None	0
901	Gordon Ado	32	F	Santa Rosa	0
512	Bill Byson	21	M	Santa Rosa	0
411	Bob Iton	None	M	San Francisco	0