delete rows using sql 'like' command using data from another table

I am trying to delete rows from a table ("lovalarm") where a field ("pointid") is like any one of a number of strings.

Currently I am entering them all manually however I need to be able to have a list of over 100,000 options.

My thoughts are to have a table ("lovdata") containing all possible strings and running a query to delete rows where the field is 'like' any of the strings in the other 开发者_开发知识库table.

Can anyone point me in the right direction as to if/how I can use like in this way?

Many thanks, Cap

sure you can join with the LIKE operator:

DELETE FROM lovalarm a 
 WHERE EXISTS (SELECT NULL 
                 FROM lovdata d 
                WHERE a.pointid LIKE d.pointid)

The lovdata.pointid column may contain wildcards, consider:

SQL> CREATE TABLE lovalarm AS
  2  SELECT 'AA' pointid FROM dual
  3  UNION ALL SELECT 'AB' FROM dual
  4  UNION ALL SELECT 'AC' FROM dual
  5  UNION ALL SELECT 'BA' FROM dual
  6  UNION ALL SELECT 'BB' FROM dual
  7  UNION ALL SELECT 'BC' FROM dual;    

Table created

SQL> --# suppose that you want to remove all entries that start
  2  --# with A or end with C
  3  CREATE TABLE lovdata AS
  4  SELECT 'A%' pointid FROM dual
  5  UNION ALL SELECT '%C' FROM dual;

Table created

SQL> DELETE FROM lovalarm a
  2   WHERE EXISTS (SELECT NULL
  3                   FROM lovdata d
  4                  WHERE a.pointid LIKE d.pointid);

4 rows deleted

SQL> select * from lovalarm;

POINTID
-------
BA
BB

Just use a sub query, like this:

DELETE 
  FROM lovalarm
 WHERE pointid IN (SELECT mystring FROM lovdata)

EDIT: Just noticed that LIKE is required, so IN isn't going to work as this will be exact matches. I'll leave this here for reference as Vincent has already added the correct query.

here is an example with the % concatenations

CREATE TABLE LOVALARM(POINTID VARCHAR2(50) ,TXT VARCHAR2(50));
create table deleteThese(deleteStringList varchar2(50));
/
INSERT INTO LOVALARM(POINTID, TXT) VALUES('abc def','1');
INSERT INTO LOVALARM(POINTID, TXT) VALUES('def','2');
INSERT INTO LOVALARM(POINTID, TXT) VALUES('abc','3');
INSERT INTO LOVALARM(POINTID, TXT) VALUES('efd','4');
INSERT INTO LOVALARM(POINTID, TXT) VALUES('abb','5');
INSERT INTO LOVALARM(POINTID, TXT) VALUES('efd bbb','6');
INSERT INTO LOVALARM(POINTID, TXT) VALUES('abb ccc','7');
INSERT INTO LOVALARM(POINTID, TXT) VALUES('abbbccc','8');


INSERT INTO DELETETHESE(DELETESTRINGLIST) VALUES('abc');
INSERT INTO DELETETHESE(DELETESTRINGLIST) VALUES('bbb');

COMMIT;
/

DELETE LOVALARM 
 where exists(
            SELECT NULL
              FROM DELETETHESE
             where LOVALARM.pointid like '%' || dELETETHESE.DELETESTRINGLIST  || '%'
        );

select * from LOVALARM;

POINTID                                            TXT                                                
-------------------------------------------------- -----
def                                                2                                                  
efd                                                4                                                  
ABB                                                5                                                  
abb ccc                                            7