I am trying to write a delete statement that will read from one table and if there is a match it will delete from a different table and that works fine but I need to do a join or something similar because the 2 tables do not have a column that is similar between them but I have a third table that has columns from both the other tables. The following code does not work because a3.lspart != a4.unfmt.
DELETE FROM KELLYP.LOSTSALE A3
WHERE EXISTS (SELECT 'unfmt' from kellyp.newpn a4
where a3.LSpart = a4.unfmt
and a3.lslncd = a4.lc
and a3.lssbrn = branch);
To help clarify the issue the table LOSTSALE and table NEWPN do not have a similar column that will work to determine what to delete but there is a third table called INSMFT that has both LSPART and UNFMT in it, so I want to match column UNFMT with table INSMFT and use that join to delete from table LOSTSALE with column lspart.
Sorry in advance I am sure this is confusing to read I appreciate the help.