Re: Delete with Multiple Field Select

Michael,

It sounds ok to me. Just the same, I would qualify the fields in order to
avoid any ambiguities:

delete from filea A where exists
(select 1 from fileb B where A.fld1 = B.fld3 and A.fld2 = B.fld4)

Best Regards,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--




On Fri, Aug 17, 2012 at 2:56 PM, Michael Ryan <michaelrtr@xxxxxxxxx>
wrote:

Is this reasonable?

delete from filea where exists
(select fld1, fld2 from fileb where fld1 = fld3 and fld2 = fld4)

I tried this with a select count(*) instead of a delete, but I'm not
getting the right number of rows.

On Fri, Aug 17, 2012 at 3:23 PM, Luis Rodriguez <luisro58@xxxxxxxxx>
wrote:

Michael,

AFAIK, you just can't use more than a column in your SELECT. It should

be

sonthing like the following

Delete from filea where
(FLD1 in (SELECT FLD3 FROM FILEB) OR
FLD1 in (SELECT FLD4 FROM FILEB) )

OR
(FLD2 in (SELECT FLD3 FROM FILEB) OR
FLD2 in (SELECT FLD4 FROM FILEB) )

HTH,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--




On Fri, Aug 17, 2012 at 2:43 PM, Michael Ryan <michaelrtr@xxxxxxxxx>

wrote:

Bah! Friday afternoon...can't think...why can't I do this?

Delete from filea where fld1, fld2 in (select fld3, fld4 from fileb)

and how would I do it?

Thanks...
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.