RE: A working SQL select bombs out when changed to delete

From: Valerio Vincenti

I need to delete from file A all the records that are matched to file B
(via a unique key field), but only those where a field on the file B
contains a specific value (Batch Number = 1060).

I started off with a select statement to make sure I target the right
records.
The following works fine:

select T6C8N1 from PPRPTEST/BBT6CPP as DCL
inner join PPRPTEST/BFEYCPP as RF
on DCL.T6C8N1 = RF.EYG9N2
where RF.EYHDN2 = 1060

I replaced the select with a delete:

delete from PPRPTEST/BBT6CPP as DCL
inner join PPRPTEST/BFEYCPP as RF
on DCL.T6C8N1 = RF.EYG9N2
where RF.EYHDN2 = 1060

Simplest way is to use a delete where in subquery:

DELETE from PPRPTEST/BBT6CPP as DCL
Where DCL.T6C8N1 in
(SELECT RF.EYG9N2 from PPRPTEST/BFEYCPP as RF
Where RF.EYHDN2 = 1060)

(and yes, it could be a lot shorter by having PPRPTEST in your library list
and by not aliasing the fields.)

DELETE from BBT6CPP where T6C8N1 in
(SELECT EYG9N2 from BFEYCPP where EYHDN2 = 1060)

Joe