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


This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].