SQL DROP Columns & Rows: Tutorial

Lesson Transcript
Instructor: Martin Gibbs

Martin has 20 years experience in Information Systems and Information Technology, has a PhD in Information Technology Management, and a master's degree in Information Systems Management. He is an adjunct professor of computer science and computer programming.

In SQL, the DROP command is used to drop tables, views, columns, and rows, including the data associated with each dropped object. Learn how this occurs, and the distinction between dropping and deleting objects. Updated: 12/27/2021

DROP Columns & Rows

Drop it!

Perhaps one of the most visual of the SQL commands, DROP, does exactly what you think it would. It drops a table, a view, a column, or row. But not only does the command drop the object, any data associated with it's gone, as if you'd dropped it off a bridge into a river.

Before we dig into the syntax of the commands for dropping columns and/or rows, let's quickly review the database table structure:

In a database, the column refers to the data field, while the row is a record of data. In a database table, the columns spread across the screen, while the rows go down (think 'row-row-row your data gently down the table').

Sometimes there's a need in SQL to remove a column or a row from a table: Removing a column requires the ALTER TABLE command; deleting a row makes use of DELETE.

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: SQL DROP Constraint: Tutorial & Overview

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:04 Drop Columns & Rows
  • 0:53 Remove a Column
  • 1:14 Some Examples
  • 1:48 Delete a Row of Data
  • 3:08 Lesson Summary
Save Save Save

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Log in or Sign up

Timeline
Autoplay
Autoplay
Speed Speed

Remove a Column

In SQL Server and Oracle databases, the syntax is:


ALTER TABLE table_name DROP COLUMN column_name;


In MySQL, the syntax is:


ALTER TABLE table_name DROP column_name;


Note that all commands end with a semicolon ( ; ). This terminates the line, telling the computer that it should complete the command. If the semicolon is misplaced or omitted, the code will not function properly.

Some Examples

To apply to a real-world example, consider the figure Customer Table. The goal is to drop the SSN column because of a request from information security.

columns example

In SQL Server and Oracle databases, the syntax is:


ALTER TABLE customer_data DROP COLUMN SSN;


In MySQL, the syntax is:


ALTER TABLE customer_data DROP SSN;


It's important to consider some things here, too. When you drop the column from the table, all of the data is deleted also! This can't be undone!

If the column is a primary key or a foreign key to another table, it cannot be deleted. You'll first have to remove these dependencies before deleting.

Delete a Row of Data

When dropping a row of data, you're in effect deleting that data record from the table and the database.

The syntax is as follows:


DELETE FROM table_name WHERE condition;


Note that the command uses the WHERE condition: This is required to specify which records are to be deleted.

In the table Customer Table 2, we'll remove the records for John Doe.

customer table 2


To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it now
Create an account to start this course today
Used by over 30 million students worldwide
Create an account