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

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