delete record from table a if it matches record in table b

D

denise

I'm having trouble getting this to work:
delete A.* from A where exists(select * from A, B
where a.empno = b.a_empno and a.paycode = b.a_paycode and a.hours1 =
b.a_hours1 and a.date1 = b.a_date1 and a.pos = b.a_pos);
It has to match all fields in both tables not just one. This is giving me
all of them, not just the matches.
Thanks for any ideas.
 
J

John W. Vinson

I'm having trouble getting this to work:
delete A.* from A where exists(select * from A, B
where a.empno = b.a_empno and a.paycode = b.a_paycode and a.hours1 =
b.a_hours1 and a.date1 = b.a_date1 and a.pos = b.a_pos);
It has to match all fields in both tables not just one. This is giving me
all of them, not just the matches.
Thanks for any ideas.
Click to expand...

If you create a unique Index on the combination of all five fields, then you
should be able to:

DELETE A.*
FROM A INNER JOIN B
ON a.empno = b.a_empno and a.paycode = b.a_paycode
and a.hours1 = b.a_hours1 and a.date1 = b.a_date1 and a.pos = b.a_pos;

What are you trying to accomplish? I wonder if a UNION query or an Append
might be a better option!

John W. Vinson [MVP]
 
Top