SQL Reference Guide > SQL Reference Guide > Understanding the Elements of SQL Statements > Subqueries > Subqueries in the Target List, SET and VALUE Clauses (Scalar Subqueries)
Was this helpful?
Subqueries in the Target List, SET and VALUE Clauses (Scalar Subqueries)
Scalar subqueries (see Scalar Subqueries) allow for the result of a query to be used in other expression contexts such as in the target list, as in the examples below.
Example query using target list scalar subquery:
SELECT s.i,(SELECT p.j FROM ptbl p WHERE s.i=p.i) FROM stbl s
Example UPDATEs using a scalar subquery in the SET clause:
UPDATE stbl s SET s.j=(SELECT p.i FROM ptbl p WHERE s.i=p.i)
UPDATE stbl s SET s.i = (SELECT p.i FROM ptbl p WHERE s.i=p.i) WHERE (SELECT p.i FROM ptbl p WHERE s.i=p.i) IS NOT NULL;
Example INSERT using a scalar subquery in the VALUES clause:
Only non-correlated subquery in an INSERT VALUES clause.
INSERT INTO stbl (I, j)
       VALUES ( (SELECT MAX(p.i) FROM ptbl p, stbl s WHERE s.i = p.i ), 1);
Note:  In the INSERT VALUES clause there is no outer row to correlate between the table into which rows are being inserted and the tables in the VALUES clause.
Last modified date: 08/29/2024