Home » SQL & PL/SQL » SQL & PL/SQL » Transfor FOR LOOP in single INSERT statement
Transfor FOR LOOP in single INSERT statement [message #191800] Fri, 08 September 2006 03:33 Go to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
Hi everybody!
I have this FOR..LOOP statement:

FOR p IN (SELECT GRP_ID, PERSON_ID FROM TABLE_1 WHERE TRANS_NR = 1) LOOP
  UPDATE TABLE_2
     SET GRP_NO = p.grp_id
   WHERE P_ID = p.PERSON_ID
END LOOP;


I'm wondering if it is possible to transform it in a single UPDATE command. Something like:

UPDATE TABLE_2 PK
   SET PK.GRP_NO = (SELECT GRP_ID
                      FROM TABLE_1
                     WHERE TRANS_NR = 1
                       AND ID_PRATICA = PK.ID_PRATICA)
 WHERE PK.P_ID IN (SELECT PERSON_ID
                     FROM TABLE_1
                    WHERE TRANS_NR = 1);


Am I correct? I in fact don't have the possibility to run the UPDATE.
Thanks a lot,
G.
Re: Transfor FOR LOOP in single INSERT statement [message #191824 is a reply to message #191800] Fri, 08 September 2006 04:55 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi

You say that You cannot test....
And then You ask people You do not know to write the update statement for You!

Wink OK

Now, I do not have Your tables, so this is not tested either, but haw about this:

UPDATE TABLE_2 a, TABLE_1 p
SET a.GRP_NO = p.grp_id
WHERE a.P_ID = p.PERSON_ID
AND p.TRANS_NR = 1


Br
Kim
Re: Transfor FOR LOOP in single INSERT statement [message #191830 is a reply to message #191800] Fri, 08 September 2006 05:15 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
Well, I was not able to test my statement at the time I wrote the post.
As for now, I tested and I found the LOOP is is performing faster...

Wink
Re: Transfor FOR LOOP in single INSERT statement [message #191831 is a reply to message #191830] Fri, 08 September 2006 05:17 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi

Using a loop can will be faster than going it right in plain sql.

But if You are satisfied, who cares Smile

Br
Kim
Re: Transfor FOR LOOP in single INSERT statement [message #191838 is a reply to message #191831] Fri, 08 September 2006 05:36 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
In fact that is what I said: the loop IS performing FASTER.
And thus, I'm happy with it.

Wink
Re: Transfor FOR LOOP in single INSERT statement [message #191839 is a reply to message #191838] Fri, 08 September 2006 05:40 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Sorry, I forgot a NOT.

Plain SQL will allways be faster than prcedural code.
Doing x number of updates is slower then doing a single update, where all x rows are updated, provided that the single update statement are written correctly to use the indexes etc.

Again, sorry for the confusion.

Br
Kim
Re: Transfor FOR LOOP in single INSERT statement [message #191842 is a reply to message #191839] Fri, 08 September 2006 05:57 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
You're right.
Here is what I'll be executing:

UPDATE TABLE_2 PK
   SET PK.GRP_NO = (SELECT GRP_ID
                      FROM TABLE_1
                     WHERE TRANS_NR = 1
                       AND ID_PRATICA = PK.ID_PRATICA)
 WHERE EXISTS (SELECT PERSON_ID
                 FROM TABLE_1
                WHERE TRANS_NR = 1
                  AND ID_PRATICA = PK.ID_PRATICA);
Re: Transfor FOR LOOP in single INSERT statement [message #191861 is a reply to message #191842] Fri, 08 September 2006 07:17 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi

You still go to the table twice, which I do not think is needed.

How about something like this:

UPDATE (select pk.grp_no, a.grp_id
from table2 pk, table1 a
where a.TRANS_NR = 1
and a.ID_PRATICA = PK.ID_PRATICA)
set GRP_NO = GRP_ID

Br
Kim
Re: Transfor FOR LOOP in single INSERT statement [message #191863 is a reply to message #191861] Fri, 08 September 2006 07:31 Go to previous message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
That's it! Smile
Previous Topic: oracle 8i documentation.
Next Topic: size of tables
Goto Forum:
  


Current Time: Wed Dec 07 18:38:49 CST 2016

Total time taken to generate the page: 0.05765 seconds