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
Senior Programmer/Analyst
Judd Wire, Inc.
124 Turnpike Road
Turners Falls, MA 01376
413-863-4357 x444
mnaughton@xxxxxxxxxxxx