Updating table_2 data using table_1 and table

Scenario

[email protected] needs to have the same user profiles in table 2 as [email protected].

Currently [email protected] have user_profile 50 instead of having user_profile 10 and 20.

Description

I want to create a query that enters into Table_2 the same id_profiles for user_email [email protected] as currently have [email protected].

The only knowledge that we have is user_email for both users and table/column names.

Table 1

id_user	user_email
1	[email protected]
2	[email protected]

Table 2

id_user	user_profile
1	10
1	20
2	50

Table 2 (expected data):

id_user	user_profile
1	10
1	20
2	10
2	20

Below query doesn't work as expected as it only inserts data when there are no rows with user_id '2' in Table_2.

DECLARE @adminEmail nvarchar(255) = '[email protected]'
DECLARE @userEmail nvarchar(255) = '[email protected]'
DECLARE @adminEmailID int = (SELECT id_user FROM Table_1 
                             WHERE user_profile = @adminEmail);

INSERT INTO Table_2 (id_user, user_profile)
    SELECT 
        (SELECT id_user FROM Table_1 
         WHERE user_email = @userEmail) AS id_user, 
        b.user_profile
    FROM 
        Table_2 b
    INNER JOIN 
        Table_1 a ON a.id_user = b.id_user
    WHERE 
        NOT EXISTS (SELECT * FROM Table_2
                    WHERE id_user = (SELECT id_user FROM Table_1 
                                     WHERE user_email = @userEmail))

CodePudding user response：

You can use MERGE for this.

Note the following:

You must pre-filter the source and target tables, using CTEs, views or derived tables, because otherwise you will end up deleting the whole table. Do not fall into the trap of putting all the conditions in the ON.
You need to find the ID of the target user, in order to insert against that ID. You cannot do this in the MERGE as there will be no matching row due to the JOIN.

DECLARE @adminEmail nvarchar(255) = '[email protected]';
DECLARE @userEmail nvarchar(255) = '[email protected]';
DECLARE @userEmailID int = (SELECT id_user FROM Table_1
                             WHERE user_email = @userEmail);
                             
WITH Source AS (
    SELECT t2.*
    FROM Table_2 t2
    JOIN Table_1 t1 ON t1.id_user = t2.id_user
    WHERE t1.user_email = @adminEmail
),
Target AS (
    SELECT t2.*
    FROM Table_2 t2
    WHERE t2.id_user = @userEmailID
)
MERGE Target t
USING Source s ON s.user_profile = t.user_profile
WHEN NOT MATCHED BY TARGET THEN
    INSERT (id_user, user_profile)
    VALUES (@userEmailID, s.user_profile)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
;

db<>fiddle

Depending on the complexity of the joins, it may be easier and more performant to do this as separate INSERT and DELETE statements

DECLARE @adminEmail nvarchar(255) = '[email protected]';
DECLARE @userEmail nvarchar(255) = '[email protected]';

INSERT Table_2 (id_user, user_profile)
SELECT t1User.id_user, t2.user_profile
FROM Table_2 t2
JOIN Table_1 t1Admin ON t1Admin.id_user = t2.id_user
    AND t1Admin.user_email = @adminEmail
JOIN Table_1 t1User ON t1User.user_email = @userEmail
WHERE NOT EXISTS (SELECT 1
    FROM Table_2 t2Existing
    WHERE t2Existing.id_user = t1User.id_user
      AND t2Existing.user_profile = t2.user_profile);
      
DELETE t2
FROM Table_2 t2
JOIN Table_1 t1User ON t1User.id_user = t2.id_user
    AND t1User.user_email = @userEmail
WHERE NOT EXISTS (SELECT 1
    FROM Table_2 t2Admin
    JOIN Table_1 t1Admin ON t1Admin.id_user = t2Admin.id_user
    WHERE t1Admin.user_email = @adminEmail
      AND t2Admin.user_profile = t2.user_profile);

db<>fiddle