Copyright

SQL DROP Columns & Rows: Tutorial

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
Add to Add to Add to

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
Lesson Transcript
Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

This lesson reviews the syntax for dropping columns and rows from a database using SQL. Deleting a column removes a data field; removing a row deletes a record of data.

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.

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 risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create An Account
Support