SQL - insert into table if value exists in another table

Question

I am trying to get the sql query below to work but I am getting an error, my problem is as follows:

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

This is what I have but when I try to execute I get an error on the line with the IF saying ORA-06550: line 8, column 15:PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:( - + case mod new no.....

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 suspect that your if statement is the culprit, because what you are trying to achieve should definitely be possible. Can you please try the following 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 it helps you figure out what the problem is.

Answer 2

I've written it really quick, but I think that this two queries will solve your problem without loop (which is slower then one time insert):

-- 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. don't you forget about tablec in else?

Answer 3

I've seen it done this way; but not the select with return value embeded 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;