Oracle SQL Update column from another table

I have two tables the first one is EMPLOYEES with column REGISTRATION_NUMBER varchar2(50) is nullable and the second table is TEMP with the same column.
I’m trying to update my first table based on this column but i get this error: ORA-01427: single-row subquery returns more than one row.

my query is:

UPDATE employees e
   SET (e.SOCIAL_SECURITY_NUMBER, e.IBAN, e.ID_CARD) = (SELECT t.AMKA, t.IBAN, t.ADT
                        FROM TEMP t
                        WHERE t.REGITRATION_NUMBER = e.REGITRATION_NUMBER)
 WHERE EXISTS (
    SELECT 1
    FROM TEMP t
    WHERE t.REGITRATION_NUMBER = e.REGITRATION_NUMBER)

>Solution :

TEMP table contains more than a single record for (at least some of) REGITRATION_NUMBER value(s).

For example, as you can’t put e.g. 2 (or more) AMKA values into a single SOCIAL_SECURITY_NUMBER, the question is: which one do you want, then?

One option is to fix WHERE clause and add additional condition(s) which will make sure that subquery returns only one row.

For sample data:

SQL> SELECT * FROM employees ORDER BY regitration_number;

REGITRATION_NUMBER SOCIAL_SEC IBAN          ID_CARD
------------------ ---------- ---------- ----------
                 1
                 2

SQL> SELECT * FROM temp ORDER BY regitration_number;

REGITRATION_NUMBER AMKA    IBAN            ADT
------------------ ------- -------- ----------
                 1 SSN 1   IBAN 1          100  --> two rows for
                 1 SSN 1-2 IBAN 1-2        101  --> the same regitration_number
                 2 SSN 2   IBAN 2          200

Your query returns an error (as you already know):

SQL> UPDATE employees e
  2     SET (e.SOCIAL_SECURITY_NUMBER, e.IBAN, e.ID_CARD) = (SELECT t.AMKA, t.IBAN, t.ADT
  3                          FROM TEMP t
  4                          WHERE t.REGITRATION_NUMBER = e.REGITRATION_NUMBER)
  5   WHERE EXISTS (
  6      SELECT 1
  7      FROM TEMP t
  8      WHERE t.REGITRATION_NUMBER = e.REGITRATION_NUMBER);
   SET (e.SOCIAL_SECURITY_NUMBER, e.IBAN, e.ID_CARD) = (SELECT t.AMKA, t.IBAN, t.ADT
                                                        *
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row


SQL>

Two options you might want to consider.

Update; rownum (line #6) makes sure that only one row is being fetched:

SQL> UPDATE employees e
  2     SET (e.SOCIAL_SECURITY_NUMBER, e.IBAN, e.ID_CARD) =
  3            (SELECT t.AMKA, t.IBAN, t.ADT
  4               FROM TEMP t
  5              WHERE     t.REGITRATION_NUMBER = e.REGITRATION_NUMBER
  6                    AND ROWNUM = 1)
  7   WHERE EXISTS
  8            (SELECT 1
  9               FROM TEMP t
 10              WHERE t.REGITRATION_NUMBER = e.REGITRATION_NUMBER);

2 rows updated.

SQL> SELECT * FROM employees ORDER BY regitration_number;

REGITRATION_NUMBER SOCIAL_SEC IBAN          ID_CARD
------------------ ---------- ---------- ----------
                 1 SSN 1      IBAN 1            100
                 2 SSN 2      IBAN 2            200

merge, which uses row_number analytic function to sort rows by something (I didn’t sort them by anything, so I’ll pick only one – any one by line #12):

SQL> ROLLBACK;

Rollback complete.

SQL> MERGE INTO employees e
  2       USING (SELECT t.regitration_number,
  3                     t.AMKA,
  4                     t.IBAN,
  5                     t.ADT,
  6                     ROW_NUMBER () OVER (PARTITION BY regitration_number ORDER BY NULL)   rn
  7                FROM TEMP t) x
  8          ON (x.regitration_number = e.regitration_number)
  9  WHEN MATCHED
 10  THEN
 11     UPDATE SET e.social_security_number = x.amka, e.iban = x.iban, e.id_card = x.adt
 12             WHERE x.rn = 1;

2 rows merged.

SQL> SELECT * FROM employees ORDER BY regitration_number;

REGITRATION_NUMBER SOCIAL_SEC IBAN          ID_CARD
------------------ ---------- ---------- ----------
                 1 SSN 1      IBAN 1            100
                 2 SSN 2      IBAN 2            200

SQL>