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

Valencio,

Just do muddy the waters a bit for you, there is a way to perform your
delete using inner join, although you wouldn't necessarily recognize it as
such.
I'll explain what I mean, but I'd still go with Joe's solution in the end.

You started figuring out what to delete by running the SELECT statement
using INNER JOIN (everybody sane does the same thing :)):

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

Statement above could be rewritten as an old-school join defined in a WHERE
clause:

select T6C8N1
from PPRPTEST/BBT6CPP as DCL, PPRPTEST/BFEYCPP as RF
where DCL.T6C8N1 = RF.EYG9N2 and RF.EYHDN2 = 1060

You get the same result set and join IS performed, although no explicit JOIN
syntax is specified. Reason I bring it up is because you can use the WHERE
type joins in DELETE & UPDATE statements as well. For example:

DELETE FROM PPRPTEST/BBT6CPP as DCL
WHERE EXISTS
(SELECT 1
FROM PPRPTEST/BFEYCPP as RF
where DCL.T6C8N1 = RF.EYG9N2 and RF.EYHDN2 = 1060)

As you can see, join is performed and only rows you want are deleted from
the primary table. It's nice to have this technique in your arsenal in case
the simpler IN flavor Joe outlined doesn't perform to your specification.
This would give you another thing to try.

HTH.

Elvis

Mike Cain - DB2 for i5/OS Temporary Indexes - The Good, The Bad, The Ugly
October 16
2007 System i Fall Technical Conference | Orlando | November 4-7
Celebrating 10-Years of SQL Performance Excellence on IBM System i, eServer
iSeries and the server affectionately known as the AS/400

-----Original Message-----
Subject: Re: A working SQL select bombs out when changed to delete

Thank you very much Joe and Michael for providing the solution to my problem
(a special thank you to Michael for taking the time to explain why the inner
join wouldn't work. I needed that!).

"Michael Naughton" <michael_naughton@xxxxxxxxxxxx> >>>

Joe gave exactly the right answer about how to do this, but to follow up a
little on the question of why Valerio's statement didn't work: you can't

delete from FILEA inner join FILEB

because "FILEA inner join FILEB" isn't a file (or "table", to use SQL-speak
:-) -- it's a collection of rows and columns returned by the SQL statement.

Instead, you have to

delete from FILEA (cleans out FILEA completely)

- or -

delete from FILEA where <some conditions here> (removes only
some of the rows in FILEA)


In this case, since you want to delete rows in FILEA that satisfy some
conditions involving FILEB, a subquery in the "where" clause does the trick.

HTH,

Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> writes:

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

Mike Naughton