On 17 Aug 2012 12:47, Michael Schutte wrote:
I know on the select you need to put the fields in the from file in
parenthesis. So I think you can do the same on the delete SQL.
Delete from filea
where (fld1, fld2) in (select fld3, fld4 from fileb)
Correct; since whatever release the following syntax is supported:
DELETE FROM table-name
WHERE ( row-value-expression ) IN ( fullselect )
At least since v5r4, that row-value-expression [a column\expression
list enclosed in parenthesis] can be used for the IN predicate.
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstch2pred.htm
"
_Predicates_
A predicate specifies a condition that is true, false, or unknown about
a given row or group. ...
_Row-value expression_ : The operand of several predicates (basic,
quantified, and IN) can be a row-value-expression:
.-,----------.
V |
>>-(-----expression-+--)-------------------------><
A row-value-expression returns a single row that consists of one or more
column values. The values can be specified as a list of expressions. The
number of columns that are returned by the row-value-expression is equal
to the number of expressions that are specified in the list.
_IN predicate_
>>-+-expression--+-----+--IN--+-(--fullselect--)--------+-----+-><
| '-NOT-' | .-,-------------. | |
| | V | | |
| +-(-----expression--+--)--+ |
| '---expression------------' |
'-(--row-value-expression--)--+-----+-IN--(--fullselect--)-'
'-NOT-'
...
When a row-value-expression is specified, the IN predicate compares
values with a collection of values.
≥ SELECT * is not allowed in the outermost select lists of the
fullselect.
≥ The result table of the fullselect must have the same number of
columns as the row-value-expression. The data types of the corresponding
expressions of the row-value-expression and the fullselect must be
compatible. The value of each expression on the left side is compared
with the value of its corresponding expression on the right side.
The value of the predicate depends on the operator that is specified:
≥ When the operator is IN, the result of the predicate is:
o True if at least one row returned from the fullselect is
equal to the row-value-expression.
o False if the result of the fullselect is empty or if no row
returned from the fullselect is equal to the row-value-expression.
o Otherwise, unknown (that is, if the comparison of
row-value-expression to the row returned from the fullselect evaluates
to unknown because of a null value for at least one row returned from
the fullselect and no row returned from the fullselect is equal to the
row-value-expression).
<<SNIP the NOT IN and other caveats\warnings\information, including
equivalent quantified predicate; see docs>>
...
"
See also "Quantified predicate" using ALL, SOME, or ANY
Since v6r1 the InfoCenter docs drill down to specific predicates:
IBM i 6.1 Information Center -> Database -> Reference -> SQL reference
-> Language elements -> Predicates
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzch2pred.htm
-> Quantified predicate
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzquantified.htm
-> IN predicate
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzin.htm
Regards, Chuck
midrange.com
