update a record from a table with another table's data then return the result-CodePudding

As I said in the title, I need to "update a record from a table with another table's data then return the result". I want change multiple rows and I already accomplished that with this query:

UPDATE SBCONN.OF_ENTITY_ACCOUNT_SITE ACCS
    SET ACCOUNT_NUMBER = (
        SELECT 
            'PE-' || AC.DOCUMENT_NUMBER || '-' || AC.ENTITY_ID
        FROM 
            SBCONN.OF_ENTITY_ACCOUNT AC 
        WHERE 
                AC.COUNTRY_CODE = 7 
            AND ACCS.ENTITY_ID = AC.ENTITY_ID
            AND ACCS.ACCOUNT_NUMBER NOT LIKE 'PE%'
            AND ACCS.LAST_INT_DATE BETWEEN TO_DATE('25/06/2018', 'DD/MM/YYYY') 
            AND TO_DATE('27/06/2018', 'DD/MM/YYYY')
    )
    WHERE EXISTS (
        SELECT 1 FROM SBCONN.OF_ENTITY_ACCOUNT AC 
        WHERE 
                AC.COUNTRY_CODE = 7 
            AND ACCS.ENTITY_ID = AC.ENTITY_ID
            AND ACCS.ACCOUNT_NUMBER NOT LIKE 'PE%'
            AND ACCS.LAST_INT_DATE BETWEEN TO_DATE('25/06/2018', 'DD/MM/YYYY') 
            AND TO_DATE('27/06/2018', 'DD/MM/YYYY')
    );

However, I also want to return some informations from the table I updated the data. Searching, I found the following sollution, yet I got error in the 'SELECT [...] INTO info' line, if I don't put the INTO clause, the ide complains, but if I put it, I receive the error: "PL/SQL: ORA-01744: INTO inappropriate".

DEClARE
    acc_id NUMBER;
    acc_num VARCHAR2(50 BYTE);
BEGIN
    UPDATE SBCONN.OF_ENTITY_ACCOUNT_SITE ACCS
        SET ACCOUNT_NUMBER = (
            SELECT 
                'PE-' || AC.DOCUMENT_NUMBER || '-' || AC.ENTITY_ID INTO info
            FROM 
                SBCONN.OF_ENTITY_ACCOUNT AC 
            WHERE 
                    AC.COUNTRY_CODE = 7 
                AND ACCS.ENTITY_ID = AC.ENTITY_ID
                AND ACCS.ACCOUNT_NUMBER NOT LIKE 'PE%'
                AND ACCS.LAST_INT_DATE BETWEEN TO_DATE('25/06/2018', 'DD/MM/YYYY') 
                AND TO_DATE('27/06/2018', 'DD/MM/YYYY')
        )
        WHERE
                AC.COUNTRY_CODE = 7 
            AND ACCS.ENTITY_ID = AC.ENTITY_ID
            AND ACCS.ACCOUNT_NUMBER NOT LIKE 'PE%'
            AND ACCS.LAST_INT_DATE BETWEEN TO_DATE('25/06/2018', 'DD/MM/YYYY') 
            AND TO_DATE('27/06/2018', 'DD/MM/YYYY')
        RETURNING 
            CUSTOMER_ACCOUNT_ID, ACCOUNT_NUMBER
        INTO
            acc_id, acc_num;
END;

Could you help me, please.

CodePudding user response：

Works for me, after removing the 'into info' from the subquery:

create table demo (id, val) as
select rownum, column_value 
from   sys.ora_mining_varchar2_nt('Red','Yellow','Orange','Blue');

select * from demo;

   ID VAL
----- ----------
    1 Red
    2 Yellow
    3 Orange
    4 Blue

declare
    colour demo.val%type;
begin
    update demo d
    set    d.val = d.val
    where  d.id in
           ( select d.id from demo d order by dbms_random.value
             fetch first row only )
    returning d.val into colour;
    
    dbms_output.put_line(colour); 
end;
/

Yellow

If you want to capture more than one value, you will need to bulk collect into a collection.

declare
    type stringlist is table of demo.val%type;
    colours stringlist;
begin
    update demo d
    set    d.val = d.val
    where  d.id in
           ( select d.id from demo d order by dbms_random.value
             fetch first 2 rows only )
    returning d.val bulk collect into colours;
    
    for i in colours.first..colours.last loop
        dbms_output.put_line(colours(i));
    end loop; 
end;
/

Blue
Red

CodePudding user response：

Try the select "select into info" statement outside of update and then simply run

 UPDATE SBCONN.OF_ENTITY_ACCOUNT_SITE ACCS
 SET ACCOUNT_NUMBER = info
 WHERE ...

Dont forget to declare the info variable:

info varchar2(100);

with that change your whole code would look like this:

 DEClARE
        acc_id NUMBER;
        acc_num VARCHAR2(50 BYTE);
        info varchar2(100);
    BEGIN
                SELECT 
                    'PE-' || AC.DOCUMENT_NUMBER || '-' || AC.ENTITY_ID INTO info
                FROM 
                    SBCONN.OF_ENTITY_ACCOUNT AC 
                WHERE 
                        AC.COUNTRY_CODE = 7 
                    AND ACCS.ENTITY_ID = AC.ENTITY_ID
                    AND ACCS.ACCOUNT_NUMBER NOT LIKE 'PE%'
                    AND ACCS.LAST_INT_DATE BETWEEN TO_DATE('25/06/2018', 'DD/MM/YYYY') 
                    AND TO_DATE('27/06/2018', 'DD/MM/YYYY');
                    
        UPDATE SBCONN.OF_ENTITY_ACCOUNT_SITE ACCS
            SET ACCOUNT_NUMBER = info
            WHERE
                    AC.COUNTRY_CODE = 7 
                AND ACCS.ENTITY_ID = AC.ENTITY_ID
                AND ACCS.ACCOUNT_NUMBER NOT LIKE 'PE%'
                AND ACCS.LAST_INT_DATE BETWEEN TO_DATE('25/06/2018', 'DD/MM/YYYY') 
                AND TO_DATE('27/06/2018', 'DD/MM/YYYY')
            RETURNING 
                CUSTOMER_ACCOUNT_ID, ACCOUNT_NUMBER
            INTO
                acc_id, acc_num;
    END;