• Choose a category
• All
• Classic-Fiction

* * *

Input

INSERT INTO customers(cust_id,

cust_contact,

cust_email,

cust_name,

cust_address,

cust_city,

cust_state,

cust_zip,

cust_country)

SELECT cust_id,

cust_contact,

cust_email,

cust_name,

cust_address,

cust_city,

cust_state,

cust_zip,

cust_country

FROM custnew;

Analysis

This example uses INSERT SELECT to import all the data from custnew into customers. Instead of listing the VALUES to be inserted, the SELECT statement retrieves them from custnew. Each column in the SELECT corresponds to a column in the specified columns list. How many rows will this statement insert? That depends on how many rows are in the custnew table. If the table is empty, no rows are inserted (and no error is generated because the operation is still valid). If the table does, in fact, contain data, all that data is inserted into customers.

This example imports cust_id (and assumes that you have ensured that cust_id values are not duplicated). You could also simply omit that column (from both the INSERT and the SELECT) so MariaDB would generate new values.

* * *

Tip: Column Names in INSERT SELECT

This example uses the same column names in both the INSERT and SELECT statements for simplicity’s sake. But there is no requirement that the column names match. In fact, MariaDB does not even pay attention to the column names returned by the SELECT. Rather, the column position is used, so the first column in the SELECT (regardless of its name) is used to populate the first specified table column, and so on. This is useful when importing data from tables that use different column names.

* * *

The SELECT statement used in an INSERT SELECT can include a WHERE clause to filter the data to be inserted.

* * *

Note: More Examples

Looking for more examples of INSERT use? See the example table population scripts (described in Appendix B, “The Example Tables”) used to create the example tables in this book.

* * *

Summary

In this chapter, you learned how to use INSERT to insert rows into a database table. You learned several other ways to use INSERT, and why explicit column specification is preferred. You also learned how to use INSERT SELECT to import rows from another table. In the next chapter, you learn how to use UPDATE and DELETE to further manipulate table data.

20. Updating and Deleting Data

In this chapter, you learn how to use the UPDATE and DELETE statements to enable you to further manipulate your table data.

Updating Data

To update (modify) data in a table the UPDATE statement is used. UPDATE can be used in two ways:

• To update specific rows in a table

• To update all rows in a table

Let’s take a look at each of these uses.

* * *

Caution: Don’t Omit the WHERE Clause

Special care must be exercised when using UPDATE because it is all too easy to mistakenly update every row in your table. Please read this entire section on UPDATE before using this statement.

* * *

* * *

Tip: UPDATE and Security

Use of the UPDATE statement can be restricted and controlled. More on this in Chapter 28, “Managing Security.”

* * *

The UPDATE statement is easy to use—some would say too easy. The basic format of an UPDATE statement is made up of three parts:

• The table to be updated

• The column names and their new values

• The filter condition that determines which rows should be updated

Let’s take a look at a simple example. Customer 10005 now has an e-mail address, and so his record needs updating. The following statement performs this update:

Input

UPDATE customers

SET cust_email = 'elmer@fudd.com'

WHERE cust_id = 10005;

Analysis

The UPDATE statement always begins with the name of the table being updated. In this example, it is the customers table. The SET command is then used to assign the new value to a column. As used here, the SET clause sets the cust_email column to the specified value:

SET cust_email = 'elmer@fudd.com'

The UPDATE statement finishes