Delete on joined Tables

[Linda Ward]

I need to delete records from one table while selecting the criteria from another.

On ISERIES 550

I can get a select to work but I know that deleting and updating on SQL are different.

Delete from libraryA/tableA where exists
(select from libraryA/tableB
where (tableA.fieldA = tableB.fieldA,
tableA.fieldB = tableB.fieldB,
tableB.fieldC in ('XXX', 'YYY', 'ZZZ)

I know this isn't correct but just an example as to what I am trying to accomplish.

I need to delete from tableA where the key fields in tableA and tableB are equal, as well as some additional status information is equal to what is in the list.

[ronverdonk]

The WHERE clause uses boolean operators to combine conditions, like the AND, OR, etc. Your statement should be something along these lines:

DELETE FROM tableA WHERE EXISTS
( SELECT * FROM tableB 
    WHERE tableA.fieldA = tableB.fieldA 
      AND tableA.fieldB = tableB.fieldB 
      AND tableB.fieldC IN ('XXX', 'YYY', 'ZZZ')
);

Ronald :cool: