Truck_Number INT,
Driver_ID INT)You could then issue an SQL statement that fulfills the shipper's needs. It would look like the following:INSERT INTO Shipper_Info
(Customer_Name, Address, City, Zip)
VALUES
SELECT CAT(First_Name + " " + Last_Name)
AS Name, Address, City, Zip
FROM Customers
WHERE
State = "MD"
This would add a row for every row of this resultset. If there were 100 rows of data that matched the
criteria, 100 rows would be added to the Shipper_Info table. Another point worth mentioning is that
you cannot insert data into the table from which you are selecting the data. Additionally, the number of
columns, as well as the data in the column, must match the columns and data types of the columns into
which you are going to insert the data. For example, you cannot insert rows of data that do not match
the columns in the table. If you did so, MySQL would generate an error.
MySQL has another useful ability—the ability to delay an INSERT statement until the table is no longer
in use. This would be beneficial when there are many long SELECT queries to the database that are of
higher priority than the INSERT. Remember, MySQL performs any manipulation statements before it
performs any SELECTs. So an INSERT statement naturally has higher priority than a SELECT. To take
advantage of this capability, you must use the INSERT DELAYED syntax. That syntax looks like the
following:
INSERT DELAYED INTO Customers (Last_Name) VALUES ("Newton")
The major advantage to doing this is that the new data will not be inserted until the table is free. If a long
SELECT query and multiple INSERT DELAYED statements were issued at the same time, the INSERTs
would be queued until the SELECT statement was completed. Then the INSERTs would be issued all at
once. This is better performance-wise than issuing multiple INSERTs. The major disadvantage to using
an INSERT DELAYED is that if the mysqld daemon were to shut down, all the statements that were
queued would be deleted without being executed. Fortunately, the mysqld daemon is a very stable
process and rarely, if ever, dies unexpectedly on its own. Generally, it would take a kill -9 or a reboot for
this to happen.
UPDATE
The UPDATE command you to edit the values of existing data. This command is a lot like a SELECT
statement, but the values you are selecting can be manipulated. The following is the syntax for an
UPDATE statement:
UPDATE tablename SET columnname = value WHERE x=y
As with all SQL statements, the reserved verb come first. Immediately following the verb is the name of
the table that you are going to manipulate. After the table name, the reserved word SET is used. What
follows the SET command is a list of column names and values that you want to set. For example, if you
wanted to change the City column to Dunkirk, your command would look like the following:
City = "Dunkirk"
Multiple values can exist after the SET command. Each column/value pair must be separated by a
comma. You can update every column in the table if you want with one UPDATE command. The WHERE
clause follows the column/value list. This clause designates which rows will be updated. This can be
dangerous if you do not pay close attention to what you are doing. For example, if you wanted to update
one of your customers because she got married and changed her name, you would issue the following
statement:
UPDATE Customers SET Last_Name = "Smith" WHERE Customer_ID = 12
If a WHERE clause was not used in this statement, all the values in the Last_Name column would be set
to Smith. You can see where this could be bad news. Remember that SQL does not issue any
warnings—if you are going to change something, be sure that you are changing the right things.
It is common practice to perform a SELECT statement before you UPDATE or DELETE any data. Doing
this ensures that you are going to remove or change only the data that you intend. Using the previous
example, you would issue the following SELECT statement before you issued your UPDATE statement: