Re: SQL: Why does this delete not do its job?

Almut Herzog wrote:
>
> Hi,
>
> I have a table A and B with a column ID (varchar2 and number!) which
> look like this:
>
> A.ID_A (a varchar2)
> ------
> -202
> 14265
> TARZAN
> JUNGLE
>
> B.ID_B (a number)
> ------
> -241
> -202
> -234
>
> Now I want to delete all rows from B whose ID is NOT in A:
> delete from B where to_char(ID_B) not in (select distinct ID_A from A);
>
> and thus I expect -241 and -234 to go away but they don't! I cannot
> imitate this behaviour at home with the test database, of course...
>
> Please reply by e-mail, too, it's faster and more reliable than our news
> server. TIA!
> --
> Almut Herzog
> SECTRA-Imtec AB e-mail: al-her_at_sectra.se
> Linkoeping, SWEDEN

Try this statament:

DELETE from B
WHERE
NOT EXISTS(

	   SELECT null FROM A,B
	   WHERE A.ID_A=to_char(B.ID_B)
	  )