Selecting from a table and placing in another in MySQL

Programming in MySQL – Part 6

MySQL Course

Foreword: In this part of the series, I explain how to select from one table and place in another.

By: Chrysanthus Date Published: 27 May 2015

Introduction

This is part 6 of my series, Programming in MySQL. In this part of the series, I explain how to select from one table and place in another. You should have read the previous parts of the series before reaching here, as this is a continuation.

Project Description
Start the command line tool and connect to the wholesale database with the following guidelines (the MySQL server should already be on):

cd c:\
Mysqlcom.pl (possibly in c:\Perl\lib directory)
connect "root", "localhost", 3306;
Password: sql
USE wholesale;

Create (type and execute) the following invoice table:

CREATE TABLE Invoice
(
    InvoiceID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
DateAndTime TIMESTAMP
) ENGINE = INNODB;

Create the following simple InvoiceDetails table. An Invoice table is like a sales table. An InvoiceDetails table is like a SaleDetails table.

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

So now we have an Invoice and InvoiceDetails table whose notations are the following two lines respectively:

    Invoice (InvoiceID, DateAndTime)

    InvoiceDetails(InvoiceID, ProductID)

For one invoice a customer may require three products. You will type a short program of SQL statements that will insert the one row information for the invoice table. Since the InvoiceID is autonumber, the program will read the number given by the DBMS (MySQL server) and then use it to insert the InvoiceIDs (which are the same) for the 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 be 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.

- Close the connection and quit the command line tool as follows:

close
quit

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 a table and placing in another in MySQL

Programming in MySQL – Part 6

MySQL Course

Introduction

Related Links

Comments