Selecting from One table and placing in another in Sybase
Handling Sybase 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 Sybase SQL Anywhere 12.
By: Chrysanthus Date Published: 28 Aug 2012
Introduction
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);
We already 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 the corresponding three products in the InvoiceDetails table.
Here is the program. Try it:
INSERT INTO Invoice () VALUES ();
CREATE VARIABLE var INTEGER;
SELECT MAX(InvoiceID) AS var
INTO var
FROM Invoice;
INSERT INTO InvoiceDetails (InvoiceID, ProductID) VALUES (var, 11);
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 AUTOINCREMENT, the most recently added InvoiceID is the maximum number in the column, everything being equal. So the SELECT statement reads this maximum number and aliases it to an alias-name which is the same as the name of the variable created previously. The INTO clause of the SELECT statement copies the value read from the Invoice table to the alias-name. Since the alias-name is the same as the variable name, then the alias-name and the variable name mean the same thing (refer to the same value). For the SELECT statement to work as intended, the data-type of the InvoiceID of the Invoice table must be the same as the data-type of the created variable.
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, everything being equal. 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. We take a break and continue in the next part of the series.
Chrys
Related Courses
C++ CourseRelational Database and Sybase
Windows User Interface
Computer Programmer – A Jack of all Trade – Poem
NEXT