How can I loop inside an Update statement?

Question

I want to loop inside an UPDATE statement. Is it possible to do so? Its is giving me an error in "rec" variable. If not can you show me how I can do it. This is my code where I try to do a SELECT in order to update all the rows with all the variables.

         BEGIN
            UPDATE DATMCCN0
                SET E_NOME = (
                FOR rec IN(SELECT IID FROM DATMCCN0)
                LOOP
                SELECT I_NOME FROM DAT_CCNCONFIG0 INNER 
JOIN DATMCCN0 ON DAT_CCNCONFIG0.I_NOME = DATMCCN0.CAPLIC 
    where DATMCCN0.IID = rec.IID) 
                END LOOP;
            END;

You do not loop inside. There is no need to loop at all to update rows. But if you must, you loop and update inside the loop. E.g., BEGIN... LOOP... Update... END LOOP; Look here for the examples of Update... SELECT.. : https://www.techonthenet.com/oracle/update.php Read Oracle documentation also. There are tons of examples here and on the web for update with select or update inside loop. — Art, May 24 '18 at 16:36
Possible duplicate of [update using for loop in plsql](https://stackoverflow.com/questions/18670392/update-using-for-loop-in-plsql) — WorkSmarter, May 24 '18 at 16:37
Maybe show an example of input data, and an example of results you want to get from this update. At the moment it is not clear what exactly you want to get, the statement you have shown in the question is simply wrong and cannot compile. It's very hard to guess looking at wrong and not working code what it is supossed to do. — krokodilko, May 24 '18 at 16:59
@jose Rather than making unconstructive statements like "None of you understood my question", perhaps it would be better to improve your question to help us (who do not have the intimate knowledge of your question) to understand by providing a [MCVE] including a description in English (not in code) of what you are trying to achieve, some sample data (as DDL/DML statements) and your expected result. We are volunteers and being rude is not conducive to getting us to volunteer our time to help you rather than someone else. — MT0, May 25 '18 at 09:38

score 1 · Answer 1 · answered May 24 '18 at 17:01

You are probably trying to do a correlated update.

UPDATE datmccn0 ccn 
SET    e_nome = (SELECT i_nome 
                 FROM   dat_ccnconfig0 conf 
                 WHERE  ccn.i_nome = conf.caplic 
                        AND ccn.iid = conf.iid);

This statement should solve your problem.

score 0 · Accepted Answer · answered May 25 '18 at 09:34

Use a MERGE statement:

MERGE INTO DATMCCN0 dst
USING DAT_CCNCONFIG0 src
ON (
      src.IID    = dst.IID
  AND src.I_NOME = dst.CAPLIC
)
WHEN MATCHED THEN
  UPDATE SET E_NOME = src.I_NOME;

or, depending on your logic:

MERGE INTO DATMCCN0 dst
USING (
  SELECT b.IID,
         /* MAX(I_NOME) AS */ I_NOME
  FROM   DAT_CCNCONFIG0 a
         INNER JOIN DATMCCN0 b
         ON a.I_NOME = b.CAPLIC
  -- GROUP BY b.IID
) src
ON ( src.IID = dst.IID)
WHEN MATCHED THEN
  UPDATE SET E_NOME = src.I_NOME;

How can I loop inside an Update statement?

2 Answers2

Linked