Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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) )Received on Fri Oct 25 1996 - 00:00:00 CDT