How to store data from a SELECT statement and use that data to loop with an UPDATE statement

Question

I am using PLSQL and I want to store the query results form SELECT statement in an array and then I want to loop using the elements from that array to UPDATE all the rows. The problem with the code below is that it returns a single-row. Sub-query returns more than one row because he is trying to set more than one variable in a row. Can you help me in this situation?

This is my code:

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

EXECUTE looping;

What happened with this solution I gave you : https://stackoverflow.com/a/50514628/7998591? — Kaushik Nayak, May 25 '18 at 09:21
From the error it looks like you get multiple values in your inner select for the combination of iid and caplic. You need to decide which value to use ,max, min that should be used in the update accordingly. Or another condition should be added to filter unique rows — Kaushik Nayak, May 25 '18 at 09:25

MT0 · Answer 1 · 2018-05-25T09:31:16.003

You do not need a loop and can do it all in one MERGE statement (assuming your correlated query returns a single row for each IID):

CREATE OR REPLACE PROCEDURE looping
IS
BEGIN
  MERGE INTO DATMCCN0 dst
  USING (
    SELECT b.IID,
           I_NOME
    FROM   DAT_CCNCONFIG0 a
           INNER JOIN DATMCCN0 b
           ON a.I_NOME = b.CAPLIC
  ) src
  ON ( src.IID = dst.IID)
  WHEN MATCHED THEN
    UPDATE SET E_NOME = src.I_NOME;
END;

If it does not then you will need to get only a single row, something like this:

CREATE OR REPLACE PROCEDURE looping
IS
BEGIN
  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;
END;

score 0 · Answer 2 · answered May 25 '18 at 10:06

One literal answer for your question "How to store data from a SELECT statement and use that data [to loop] with an UPDATE statement" would be a statement like this:

UPDATE 
    (SELECT src.E_NOME, dst.I_NOME
    FROM DAT_CCNCONFIG0 
        JOIN DATMCCN0 src ON DAT_CCNCONFIG0.I_NOME = scr.CAPLIC 
        JOIN DATMCCN0 dst ON src.IID = dst.IID)        
SET E_NOME = I_NOME;

However, it does not solve your problem that a single-row subquery returns more than one. Have a look at MT0's answer for that.

How to store data from a SELECT statement and use that data to loop with an UPDATE statement

2 Answers2