SQL - insert into a table if a value exists in another table

Question

I am trying to get the sql query below to work, but I get an error message, my problem is this:

I need to scroll through the result set from the select statement (this part is fine). Inside the loop for each row, I need to check if the URL exists in table A. If so, then insert the mapping into tableB, otherwise insert a new row in tableC.

This is what I have, but when I try to execute, I get an error message in a line with IF saying that ORA-06550: line 8, column 15: PLS-00103: met the character “SELECT”, expecting that one of the following :( - + case mod no new .....

DECLARE STANDARD_LINK_ID TABLEB.LINK_ID%type; BEGIN FOR LINK_ROW IN ( SELECT LINKTEXT, LINKURL, CORPID FROM OLD_DATA) LOOP IF (SELECT COUNT(URL) FROM TABLEA WHERE URL = LINK_ROW.LINKURL) = 1 THEN SELECT LINKID INTO STANDARD_LINK_ID FROM TABLEA WHERE URL = LINK_ROW.URL; INSERT INTO TABLEB(LINK_ID, CORP_ID) VALUES (STANDARD_LINK_ID, LINK_ROW.CORPID); ELSE INSERT INTO TABLEB(LINK_ID, LINK_NAME, URL, CORP_ID) VALUES (SEQ_LINK.NEXTVAL, LINK_ROW.LINKTEXT, LINK_ROW.LINKURL, LINK_ROW.CORP_ID); END IF; END LOOP; COMMIT; END; 

Answer 1

I saw how it was done like this; but not a choice with a return value nested in the condition

https://forums.oracle.com/forums/thread.jspa?threadID=177035

 DECLARE STANDARD_LINK_ID TABLEB.LINK_ID%type; DECLARE W_LINK_COUNT NUMBER (1,0); BEGIN FOR LINK_ROW IN ( SELECT LINKTEXT, LINKURL, CORPID FROM OLD_DATA) LOOP SELECT COUNT(URL) INTO W_LINK_COUNT FROM TABLEA WHERE URL = LINK_ROW.LINKURL; IF W_LINK_COUNT = 1 THEN SELECT LINKID INTO STANDARD_LINK_ID FROM TABLEA WHERE URL = LINK_ROW.URL; INSERT INTO TABLEB(LINK_ID, CORP_ID) VALUES (STANDARD_LINK_ID, LINK_ROW.CORPID); ELSE INSERT INTO TABLEB(LINK_ID, LINK_NAME, URL, CORP_ID) VALUES (SEQ_LINK.NEXTVAL, LINK_ROW.LINKTEXT, LINK_ROW.LINKURL, LINK_ROW.CORP_ID); END IF; END LOOP; COMMIT; END; 

Answer 2

I suspect that your if statement is the culprit, because what you are trying to achieve is certainly possible. You can try instead:

 DECLARE STANDARD_LINK_ID TABLEB.LINK_ID%type; URL_COUNT NUMBER(10); BEGIN FOR LINK_ROW IN ( SELECT LINKTEXT, LINKURL, CORPID FROM OLD_DATA) LOOP SELECT COUNT(URL) INTO URL_COUNT FROM TABLEA WHERE URL = LINK_ROW.LINKURL; IF URL_COUNT = 1 THEN SELECT LINKID INTO STANDARD_LINK_ID FROM TABLEA WHERE URL = LINK_ROW.URL; INSERT INTO TABLEB(LINK_ID, CORP_ID) VALUES (STANDARD_LINK_ID, LINK_ROW.CORPID); ELSE INSERT INTO TABLEB(LINK_ID, LINK_NAME, URL, CORP_ID) VALUES (SEQ_LINK.NEXTVAL, LINK_ROW.LINKTEXT, LINK_ROW.LINKURL, LINK_ROW.CORP_ID); END IF; END LOOP; COMMIT; END; 

Hope this helps you figure out what the problem is.

Answer 3

I wrote this very quickly, but I think these two queries solve your problem without a loop (which is slower than once):

 -- insert to tableb when exists in tablea insert into tableb(link_id, corp_id) select a.linkid, o.corpid from old_data o join tablea a on o.url = a.url -- insert to tablec when not exists in tablea insert into tablec(link_id, link_name, url, corp_id) select seq_link.nextval, o.linktext, o.linkurl, o.corp_id from old_data o where not exists(select 1 from tablea a where o.url = a.url) 

ps. you will not forget about tablec in else ?