SELECT SUBQUERY - using % wildcard with IN CLAUSE 1

[JMB70]

Hi,

I'm trying to write the following

DELETE
FROM TABLEA
WHERE TABLEA.ID IN (SELECT DISTINCT(ID)||'%' FROM INVALID_IDS)

The idea is that table INVALID_IDS could have an entry of '12' and so all entries starting 12xxx would be deleted. INVALID_IDS could also contain ABC so all entries starting ABCxxx would go. Unfortunatel the way I've written it, the WHERE clause does a direct comparison against the % rather than treat it as a wildcard. Any suggestions would be massively appreciated.

Thanks.

 

[carp]

How about

DELETE FROM tableA
WHERE INSTR(tablea.id,(SELECT DISTINCT id FROM invalid_ids),1) = 1;

Of course, this has the disadvantage of running a full table scan followed by a sort on invalid_ids for every row in tableA. A more efficient approach might be:

DELETE FROM tableA
WHERE id IN
   (SELECT a.id
      FROM tableA a, invalid_ids i
     WHERE a.id LIKE i.id||'%');

 

[JMB70]

Thanks Carp, second option is ideal and works exactly as I'd hoped.

You're a Star! Ta for speedy response, you've resolved a headache for me.

 

[carp]

Glad to hear it! Who needs another headache?!