Selecting from One table and placing in another in MySQL

Handling MySQL Events with Triggers and Procedures Using SQL – Part 10

Division 5

Forward: In this part of the series, we learn how to select data from one table row and place (insert or update) in a row of another table in MySQL. We are dealing with MySQL 5.1.

By: Chrysanthus Date Published: 7 Aug 2012

Introduction

This is part 10 of my series, Handling MySQL Events with Triggers and Stored Procedures Using SQL. You must have read all the different parts of the series (or equivalent tutorials from elsewhere) up to this point. In this part of the series, we learn how to select data from one table row and place (insert or update) in a row of another table in MySQL. We are dealing with MySQL 5.1.

Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.

Project Description
We already have an Invoice table whose notation is:

    Invoice (InvoiceID, DateAndTime);

Let us create an InvoiceDetails table. An Invoice table is like a sales table. An InvoiceDetails table is like a SaleDetails table.

- Start the server and connect to it; choose the database, wholesale.
- Type and execute the following statement to create the InvoiceDetails table:

CREATE TABLE InvoiceDetails
(
    InvoiceID INTEGER NOT NULL,
    ProductID INTEGER NOT NULL
);

You should have a positive feedback. There are only two columns in the table.

So now we have an InvoiceDetails table whose notation is:

    InvoiceDetails(InvoiceID, ProductID)

For one invoice a customer may require three products. We shall write a short program of SQL statements that will insert the row information for the invoice table. Since the InvoiceID is autonumber, the program will read the number given by the DBMS and then use it to insert the InvoiceIDs (which are the same) for corresponding three products in the InvoiceDetails table.

The Code
Here is the program. Try it:

INSERT INTO Invoice () VALUES ();
SET @var = NULL;
SELECT MAX(InvoiceID)
FROM Invoice
INTO @var;
INSERT INTO InvoiceDetails (InvoiceID, ProductID) VALUES (@var, 13);
INSERT INTO InvoiceDetails (InvoiceID, ProductID) VALUES (@var, 5);
INSERT INTO InvoiceDetails (InvoiceID, ProductID) VALUES (@var, 12);

Explanation of Code
The first line in the SQL code inserts a new row into the Invoice table. The parentheses are empty because for the two columns in the Invoice table, it is the DBMS that gives the values. The next statement creates the variable that will hold the most recently inserted InvoiceID in the Invoice table.

Next you have the SELECT statement. Since an InvoiceID value in the Invoice table is AUTO_INCREMENT, the most recently added InvoiceID is the maximum number in the column, everything being equal. So the SELECT statement reads this maximum number. The INTO clause of the SELECT statement copies the value read from the Invoice table to the variable of the SET statement.

The last three statements insert corresponding rows into the InvoiceDetails table. The three products are requested in one invoice, so the InvoiceID in this table for the three products has to the last one in the Invoice table. At the moment, this InvoiceID is held by the variable, @var. So this variable is in the last three statements.

The project has been accomplished.

- Stop the database, drop the connection and stop the server in one command.

I hope you are appreciating the series. We take a break here and continue in the next part.

Chrys

Broad Network

Related Articles

Selecting from One table and placing in another in MySQL

Handling MySQL Events with Triggers and Procedures Using SQL – Part 10

Division 5

Introduction

Related Links

Comments