oracle 使用 sql 'like' 命令使用另一个表中的数据删除行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4605948/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
delete rows using sql 'like' command using data from another table
提问by Captastic
I am trying to delete rows from a table ("lovalarm") where a field ("pointid") is like any one of a number of strings.
我正在尝试从表（“lovalarm”）中删除行，其中字段（“pointid”）类似于多个字符串中的任何一个。
Currently I am entering them all manually however I need to be able to have a list of over 100,000 options.
目前我正在手动输入它们，但是我需要能够拥有超过 100,000 个选项的列表。
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.
我的想法是有一个包含所有可能字符串的表（“lovdata”）并运行查询以删除该字段与另一个表中的任何字符串“相似”的行。
Can anyone point me in the right direction as to if/how I can use like in this way?
任何人都可以指出我是否/如何以这种方式使用 like 的正确方向？
Many thanks, Cap
非常感谢，卡普
回答by Vincent Malgrat
sure you can join with the LIKE operator:
确保您可以使用 LIKE 运算符加入：
DELETE FROM lovalarm a
WHERE EXISTS (SELECT NULL
FROM lovdata d
WHERE a.pointid LIKE d.pointid)
The lovdata.pointidcolumn may contain wildcards, consider:
该lovdata.pointid列可能包含通配符，请考虑：
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
回答by BG100
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.
编辑：刚刚注意到 LIKE 是必需的，所以 IN 不会起作用，因为这将是完全匹配的。我将把它留在这里以供参考，因为 Vincent 已经添加了正确的查询。
回答by Harrison
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
