Updating a table using a scalar subselect
Updating a table using a scalar subselect - validating and non validating xml parser
What should become readily apparent is that it takes significantly less lines of code to do this in SQL than in a high-level language like RPG, Cobol, C, or Java.In this example, the scalar subselect must use correlated naming (translation: qualified column names) since the emp and dep tables each have a column called dpt and these columns are compared in the WHERE clause (WHERE = b.dpt) of the scalar subselect.
As you will see later, the information from the record retrieved in the secondary file can also be used to update the current record from the primary file.This article will discuss SQL subselect, starting with basic subselect, then moving to scalar subselect, subselect with the UPDATE, INSERT, and DELETE statements, subselect and CREATE TABLE, and ending with subselect and derived table.The following tables are used in the examples in this article to illustrate how subselect works.Because of this, SQL allows you to assign a short correlated or alternate name to the table in the FROM clause of the SQL statement, and you can use this alternate name (in place of the long SQL name) to qualify column names where necessary.In the FROM clause for the outer SELECT statement (FROM emp a) and the FROM clause for the inner scalar subselect (FROM dep b), the table called emp is followed by an a, and the table called dep is followed by a b.Executing this SELECT statement returns the same rows as in the previous example: all the employees who work in a department with a department name that begins with S. In this example, what happens if an employee row exists with an invalid department number in it and the scalar subselect cannot find a matching department number in the Department Master table?
If this situation occurs, no department number is retrieved and the default value for the column will be used instead.
The rows that are retrieved are ORDERed BY employee number (emp).
At this point and before proceeding further, we need to discuss some subselect terminology.
The simplest form of subselect can be found in a SELECT statement where a subselect is used to create a selection list for the IN predicate within the WHERE clause.
Suppose you want to see all the employees who work in a department that have a department name beginning with an uppercase S.
The challenge is that the department name (the column called dnm) exists only in the Department Master table, not in the Employee Master table.