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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

denise - 07 Feb 2008 15:58 GMT

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.

Reply to this Message

John W. Vinson - 07 Feb 2008 18:03 GMT

>I'm having trouble getting this to work:
>delete A.* from A where exists(select * from A, B
[quoted text clipped - 3 lines]
>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]

Reply to this Message