Deleting Data and Tables

We often discover that data in a table is incorrect and, therefore, can no longer be used. At such times, we need to delete data from a table.

Deleting Values from a Row

Often, we will be interested in deleting a value in a row. The easiest way to accomplish this task is to use the UPDATE structure we have already discussed and set the column value to NULL, like so:

UPDATE {table_name}
SET {column_1} = NULL,
    {column_2} = NULL,
    …
    {column_last} = NULL
WHERE
  {conditional};

Here, {table_name} is the name of the table with the data that needs to be changed, {column_1}, {column_2},… {column_last} is the list of columns whose values you want to delete, and {WHERE} is a conditional statement like the one you would find in a SQL query.

Let's say, for instance, that we have the wrong email on file for the customer with the customer ID equal to 3. To fix that, we can use the following query:

UPDATE 
  customers
SET 
  email = NULL
WHERE 
  customer_id=3;

In the next section, we will learn how to delete rows from a table.

Deleting Rows from a Table

Deleting a row from a table can be done using the DELETE statement, which looks like this:

DELETE FROM {table_name}
WHERE {conditional};

Let's say, for instance, that we have to delete the details of a customer whose email is [email protected]. To perform that, we can use the following query:

DELETE FROM 
  customers
WHERE 
  email='[email protected]';

If we wanted to delete all the data in the customers table without deleting the table, we could write the following query:

DELETE FROM customers;

Alternatively, if you want to delete all the data in a query without deleting the table, you could use the TRUNCATE keyword, like so:

TRUNCATE TABLE customers;

Deleting Tables

To delete all the data in a table and the table itself, you can just use the DROP TABLE statement with the following syntax:

DROP TABLE {table_name};

Here, {table_name} is the name of the table you want to delete. If we wanted to delete all the data in the customers table along with the table itself, we would write the following:

DROP TABLE customers;

Let's perform an exercise to delete/drop the table using the DROP TABLE statement.

Exercise 1.09: Deleting an Unnecessary Reference Table

In this exercise, you will learn how to delete a table using SQL. The marketing team has finished analyzing the potential number of customers they have in every state, and they no longer need the state_populations table. To save space on the database, delete the table.

Perform the following steps to complete the exercise:

1. Open your favorite SQL client and connect to the sqlda database.
2. Run the following query to drop the state_populations table:

   DROP TABLE state_populations;

   The state_populations table should now be deleted from the database.

3. Since the table has just been dropped, a SELECT query on this table throws an error, as expected:

   SELECT 
     * 
   FROM 
     state_populations;

   You will find the error shown in the following figure:

   

Figure 1.48: Error shown as the state_populations table was dropped

Note

To access the source code for this specific section, please refer to https://packt.live/2XWLVZA.

In this exercise, we learned how to delete a table using the DROP TABLE statement. In the next activity, we will create and modify tables using SQL.

Activity 1.04: Creating and Modifying Tables for Marketing Operations

In this activity, you will test your ability to create and modify tables using SQL. You did a great job of pulling data for the marketing team. However, the marketing manager, who you helped, realized that they had made a mistake. It turns out that instead of just the query, the manager needs to create a new table in the company's analytics database. Furthermore, they need to make some changes to the data that is present in the customers table. It is your job to help the marketing manager with the table:

1. Create a new table called customers_nyc that pulls all the rows from the customers table where the customer lives in New York City in the state of New York.
2. Delete all customers in postal code 10014 from the new table. Due to local laws, they will not be eligible for marketing.
3. Add a new text column called event.
4. Set the value of the event column to thank-you party. The following is the expected output:
   

   Figure 1.49: The customers_nyc table with event set to thank-you party

5. You've told the manager that you've completed these steps. He tells the marketing operations team, who then uses the data to launch a marketing campaign. The marketing manager thanks you and then asks you to delete the customers_nyc table.

   Note

   The solution for this activity can be found via this link.

In this activity, we used different CRUD operations to modify a table as requested by the marketing manager. We will now come full circle to talk about how SQL and analytics connect.