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

[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.

 

[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.

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]