Re: deleting records that match other table

Comments embedded.
MSWEENEY wrote:
> I am trying to a DELETE that I thought would be simple but isn't.
>
> I have table A and table B. Both have a column named "Number". There
> are duplicate records in the two tables.
>

First mistake: naming a column after a data type.

> All I want to do is delete all of the records in table A that exist in
> table B. In essence where A.number = b.number.
>

Sounds simple enough.

> I tried:
>
> delete from TABLE_A
> WHERE [A].[NUMBER] = [B].[NUMBER]
>
> Got an error.
>

Gee, I wonder why? Oh, right, there is no TABLE_B or alias B listed anywhere in that statement except for your attempt to materialise values from thin air. I'm not surprised it wasn't successful.

> Any help would be great!
>

Let's include TABLE_B in that delete statement somewhere, shall we:

Delete from TABLE_A
Where "NUMBER" in (select "NUMBER" from TABLE_B);

And, gee whiz, that should make all of the matching values, and the rows attached to them, disappear. And, I'd be renaming those columns, if I were you, to something less likely to be a data type. You could even use NUMBER in the column name, like MY_NUMBER, THAT_NUMBER, SOME_OTHER_NUMBER, ID_NUMBER, I_HAVE_YOUR_NUMBER and so on. I hope you grasp the concept.  

> Thank you, Mark

My pleasure.

David Fitzjarrell Received on Wed Mar 08 2006 - 22:07:04 CST