Update MyTable with values from AnotherTable (with self join)-CodePudding

I'm relatively new to SQL and currently making some practical tasks to gain experience and got struggled with an update of my custom overview table with values from another table that contains join.

I have an overview table MyTable with column EmployeeID. AnotherTable contains data of employees with EmployeeID and their ManagerID.

I am able to retrieve ManagerName using different join methods, including:

SELECT m.first_name
FROM AnotherTable.employees e LEFT JOIN  
     AnotherTable.employees m 
     on m.EmployeeID = e.ManagerID

But I am getting stuck updating MyTable, as I usually receive errors such as "single row query returns more than one row" or "SQL command not properly ended". I've read that Oracle doesnt support joins for updating tables. How can I overcome this issue? A sample data would be:

MyTable
------------------------------
EmployeeID | SomeOtherColumns| ..
1          |     SomeData    |
2          |     SomeData    |
3          |     SomeData    |
4          |     SomeData    |
5          |     SomeData    |
------------------------------

OtherTable
-------------------------------------
EmployeeID |   Name     | ManagerID |
1          |   Steve    |     -     |
2          |   John     |     1     |
3          |   Peter    |     1     |
4          |   Bob      |     2     |
5          |   Patrick  |     3     |
6          |   Connor   |     1     |
-------------------------------------

And the result would be then:

MyTable
-------------------------------------------
EmployeeID | SomeOtherColumns |ManagerName|
1          |     SomeData     |     -     |
2          |     SomeData     |   Steve   |
3          |     SomeData     |   Steve   |
4          |     SomeData     |   John    |
5          |     SomeData     |   Peter   |
6          |     SomeData     |   Steve   |
-------------------------------------------

As one of the options I tried to use is:

update MyTable
set MyTable.ManagerName = ( 
    SELECT
        (m.name) ManagerName
    FROM 
        OtherTable.employees e 
    LEFT JOIN OtherTable.employees m ON
        m.EmployeeID = e.ManagerID 
    )

But there I get "single row query returns more than one row" error. How is it possible to solve this?

CodePudding user response：

You can use a hierarchical query:

UPDATE mytable m
SET managername = (SELECT name
                   FROM   othertable
                   WHERE  LEVEL = 2
                   START WITH employeeid = m.employeeid
                   CONNECT BY PRIOR managerid = employeeid);

or a self-join:

UPDATE mytable m
SET managername = (SELECT om.name
                   FROM   othertable o
                          INNER JOIN othertable om
                          ON (o.managerid = om.employeeid)
                   WHERE  o.employeeid = m.employeeid);

Which, for the sample data:

CREATE TABLE MyTable (EmployeeID, SomeOtherColumns, ManagerName) AS
SELECT LEVEL, 'SomeData', CAST(NULL AS VARCHAR2(20))
FROM   DUAL
CONNECT BY LEVEL <= 5;

CREATE TABLE OtherTable(EmployeeID, Name, ManagerID) AS
SELECT 1, 'Alice', NULL FROM DUAL UNION ALL
SELECT 2, 'Beryl', 1 FROM DUAL UNION ALL
SELECT 3, 'Carol', 1 FROM DUAL UNION ALL
SELECT 4, 'Debra', 2 FROM DUAL UNION ALL
SELECT 5, 'Emily', 3 FROM DUAL UNION ALL
SELECT 6, 'Fiona', 1 FROM DUAL;

Then after either update, MyTable contains:

EMPLOYEEID SOMEOTHERCOLUMNS MANAGERNAME

1 SomeData null

2 SomeData Alice

3 SomeData Alice

4 SomeData Beryl

5 SomeData Carol

EMPLOYEEID	SOMEOTHERCOLUMNS	MANAGERNAME
1	SomeData	null
2	SomeData	Alice
3	SomeData	Alice
4	SomeData	Beryl
5	SomeData	Carol

Note: Keeping this data violates third-normal form; instead, you should keep the employee name in the table with the other employee data and then when you want to display the manager's name use SELECT ... FROM ... LEFT OUTER JOIN with a hierarchical query to include the result. What you do not want to do is duplicate the data as then it has the potential to become out-of-sync when something changes.

db<>fiddle here