How can I add the outputs of one CTE to another CTE?-CodePudding

I have a database that consists of 8 records about the employees, and it saves both the Ssn which is the id of every employee and a Super_ssn that references their manager. Here is the code for creating the table:

Create Table t_employee
(
    Ssn int not null,
    Super_ssn int,
    FirstName varchar(50),
    LastName varchar(50),
    NationalCode varchar(50),
    _role varchar(50),
    Primary key(Ssn),
    Foreign Key(Super_ssn) references t_employee(Ssn)
);

I have to write a Procedure which takes two employees' id, (for example, @empId1 and @empId2), and then perform the delete operation, with the below condition: If the first employee did not have any subalterns, it would be deleted. But if it had some subalterns, they must first be added to another employee (@empId2) and then deleted.

Here is my code:

USE CompanyHierarchy

GO

Create Procedure deleteEmployees (@empId1 int, @empId2 int)
as
Begin
    Declare @subCount int;
    With CTE1 (Ssn, FirstName, LastName, Super_ssn, NationalCode, _role, _level) as
    (
        Select emp.Ssn,
                emp.FirstName,
                emp.LastName,
                emp.Super_ssn,
                emp.NationalCode,
                emp._role,
                0 as _level
        From t_employee AS emp
        Where emp.Ssn = @empId1
        Union ALL
        Select _emp.Ssn,
                _emp.FirstName,
                _emp.LastName,
                _emp.Super_ssn,
                _emp.NationalCode,
                _emp._role,
                _emp._level   1
        From t_employee _emp
        Join CTE1   C1
        on _emp.Super_ssn = C1.Ssn
    )
    Select @subCount = COUNT (*) From CTE1 c1 Where c1.Ssn <> c1.Super_ssn;
    IF @subCount = 0
    Begin
        Delete From t_employee where Ssn = @empId1;
    End

    ELSE
    Begin
         With _CTE1 (Ssn, FirstName, LastName, Super_ssn, NationalCode, _role, _level) as
        (
            Select emp.Ssn,
                    emp.FirstName,
                    emp.LastName,
                    emp.Super_ssn,
                    emp.NationalCode,
                    emp._role,
                    0 as _level
            From t_employee AS emp
            Where emp.Ssn = @empId1
            Union ALL
            Select _emp.Ssn,
                    _emp.FirstName,
                    _emp.LastName,
                    _emp.Super_ssn,
                    _emp.NationalCode,
                    _emp._role,
                    _emp._level   1
            From t_employee _emp
            Join _CTE1  _C1
            on _emp.Super_ssn = _C1.Ssn
        )

        Update _CTE1 Set Super_ssn = @empId2 Where Super_ssn <> Ssn;
    End
End

GO
EXEC deleteEmployees @empId1 = 6, @empId2 = 5;

But it shows the error that

Msg 4421, Level 16, State 1, Procedure deleteEmployees, Line 37 [Batch Start Line 3] Derived table '_CTE1' is not updatable because a column of the derived table is derived or constant.

I have also tried to implement two CTEs to select the subalterns of both employees, and the use INSERT, but I do not know how to check not to add duplicate items.

Create Procedure deleteEmployees (@empId1 int, @empId2 int)
as
Begin
    Declare @subCount int;
    With CTE1 (Ssn, FirstName, LastName, Super_ssn, NationalCode, _role, _level) as
    (
        Select emp.Ssn,
                emp.FirstName,
                emp.LastName,
                emp.Super_ssn,
                emp.NationalCode,
                emp._role,
                0 as _level
        From t_employee AS emp
        Where emp.Ssn = @empId1
        Union ALL
        Select _emp.Ssn,
                _emp.FirstName,
                _emp.LastName,
                _emp.Super_ssn,
                _emp.NationalCode,
                _emp._role,
                _emp._level   1
        From t_employee _emp
        Join CTE1   C1
        on _emp.Super_ssn = C1.Ssn
    )
    Select @subCount = COUNT (*) From CTE1 c1 Where c1.Ssn <> c1.Super_ssn;
    IF @subCount = 0
    Begin
        Delete From t_employee where Ssn = @empId1;
    End

    ELSE
    Begin
         With _CTE1 (Ssn, FirstName, LastName, Super_ssn, NationalCode, _role, _level) as
        (
            Select emp.Ssn,
                    emp.FirstName,
                    emp.LastName,
                    emp.Super_ssn,
                    emp.NationalCode,
                    emp._role,
                    0 as _level
            From t_employee AS emp
            Where emp.Ssn = @empId1
            Union ALL
            Select _emp.Ssn,
                    _emp.FirstName,
                    _emp.LastName,
                    _emp.Super_ssn,
                    _emp.NationalCode,
                    _emp._role,
                    _emp._level   1
            From t_employee _emp
            Join _CTE1  _C1
            on _emp.Super_ssn = _C1.Ssn
        )
        , _CTE2 (Ssn, FirstName, LastName, Super_ssn, NationalCode, _role, _level) as
        (
            Select emp.Ssn,
                    emp.FirstName,
                    emp.LastName,
                    emp.Super_ssn,
                    emp.NationalCode,
                    emp._role,
                    0 as _level
            From t_employee AS emp
            Where emp.Ssn = @empId1
            Union ALL
            Select _emp.Ssn,
                    _emp.FirstName,
                    _emp.LastName,
                    _emp.Super_ssn,
                    _emp.NationalCode,
                    _emp._role,
                    _emp._level   1
            From t_employee _emp
            Join _CTE2  _C2
            on _emp.Super_ssn = _C2.Ssn
        )
        Insert Into _CTE2
        Select * From _CTE1;
    End
End

I will be grateful for your help.

CodePudding user response：

Have you overcomplicated your code? Why do you traverse the hierarchy at all? Surely if you intend to delete "a" when "b" reports to "a" and "c" reports to "b", you don't intend to do anything to "c". You only need to change those reporting directly to "a" ("b" in this case). If so, you don't need CTEs to traverse the hierarchy. You have also learned bad habits and have chosen strange naming standards.

Just check for the existence of @empId1 (a generic name that does not provide any clues about how it is used - see what I mean by naming standards?) as a supervisor first and "move" (not "add") those rows to the other user parameter @empId2. In short:

update t_employee set Super_ssn = @empId2
where Super_ssn = @empId1;

delete t_employee where Ssn = @empId1;

That is all the code you need at a very basic level. Add whatever error handling you wish, perhaps some sanity checking of the parameter values, and perhaps check for existence before the update. Use of two part names (schema.table) is a best practice to develop.