Just wondering how I would change the following SQL so that the insert only happens for unique link_row instances. So if the exact same values are pulled back in the seletc I only want to do the loop and insert the first time.
BEGIN
FOR LINK_ROW IN ( SELECT LINKTEXT, LINKURL, CORPID FROM LINKS )
LOOP
//Do insert here
When I run the script I get errors on the primary key violation as the same item is being inserted twice inside the loop.
Thanks in advance
You would use the UNIQUE or DISTINCT keyword to limit your select results.
BUT it would be far better to do the insert and select in one statement.
INSERT INTO <table>
VALUES (linktext, linkurl, corpid)
SELECT UNIQUE linktext, linkurl, corpid
FROM links;
You could use the DISTINCT keyword to select only unique records from the LINKS table:
BEGIN
FOR LINK_ROW IN ( SELECT DISTINCT LINKTEXT, LINKURL, CORPID FROM LINKS )
LOOP
However, although I don't know the full extent of your query, there's usually better ways to accomplish tasks than using loops in SQL. SQL is declarative, and designed to work with sets, so you want to tell the database engine what you want to do, not how to do it.
