sql-如果不存在则插入 [英] sql - insert if not exists
本文介绍了sql-如果不存在则插入的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在使用SQL查询时遇到麻烦.如果同一行尚不存在,我需要插入一行.这是我到目前为止的内容:
I am having trouble with a sql query. I need to insert a row if the same row does not exist already. This is what I have so far:
DECLARE
BEGIN
FOR FOLDER_ROW IN (SELECT FOLDERID, USERID FROM DATA1.FOLDERS)
LOOP
IF NOT EXISTS (SELECT * FROM DATA1.FOLDER_USER WHERE FOLDER_ID = FOLDER_ROW.FOLDERID AND USER_ID = FOLDER_ROW.USERID)
INSERT INTO DATA1.FOLDER_USER (FOLDER_ID, USER_ID) VALUES (FOLDER_ROW.FOLDERID, FOLDER_ROW.USERID);
END LOOP;
COMMIT;
END;
我对sql不太熟悉,尤其是不存在的语法,因此在执行时会出现以下错误:
I would not be very familiar with sql particularly the not exists syntax so when I execute I get the following error:
ORA-06550:第37行,第11列:PLS-00103:出现符号 预期以下情况之一时,请输入"INSERT":
ORA-06550: line 37, column 11: PLS-00103: Encountered the symbol "INSERT" when expecting one of the following:
然后和或
用"then"代替"INSERT".
The symbol "then" was substituted for "INSERT" to continue.
ORA-06550: line 38, column 10:
PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:
if
ORA-06550: line 40, column 5:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
end not pragma final instantiable order overriding static
member constructor map
推荐答案
在SQL中完成所有操作,而不是将上下文切换到PL/SQL:
Do it all in SQL rather than context switching into PL/SQL:
INSERT INTO DATA1.FOLDERS
(folder_id,
user_id)
SELECT f1.folder_id,
f1.user_id
FROM DATA1.FOLDERS f1
WHERE NOT EXISTS (SELECT 1
FROM DATA1.FOLDERS f2
WHERE f1.folder_id = f2.folder_id
AND f1.user_id = f2.user_id);
这篇关于sql-如果不存在则插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文