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

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.



Comments

Popular posts from this blog

Spring Elasticsearch Operations

Network Error and Timeout on Authorize.net JS

Object oriented programming concepts (OOPs)