CODEWITHSUNDEEP
sqloracle-databaseplsql
Kaskade
Kaskade

Reputation: 795

SQL - for loop duplicate issue

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

Upvotes: 0

Views: 1242

Answers (2)

Ollie
Ollie

Reputation: 17538

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;

Upvotes: 5

Mike Dinescu
Mike Dinescu

Reputation: 55720

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.

Upvotes: 4

Related Questions