SQL: Why does this delete not do its job?

From: Almut Herzog <al-her_at_sectra.se>
Date: 1996/10/24
Message-ID: <326F6640.4A5A@sectra.se>#1/1

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 Received on Thu Oct 24 1996 - 00:00:00 CDT