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
Post a Comment