CODEWITHSUNDEEP
sql-server
user3569267
user3569267

Reputation: 1125

Update column in SQL Server

I'm a beginner in SQL Server and I have a lot of difficulties to resolve the following task: I have 2 tables: 

User_Info
    User_UID UNIQUEIDENTIFIER ROWGUIDCOL NULL DEFAULT(newid())
    FirstName VARCHAR(50)
    LastName VARCHAR(50)
    Email VARCHAR(50)

Session
    User_UID CHAR(40)
    Session_ID PK INT

In User_Info I have different User_UID corresponding to the same user, i.e. a user having the same FirstName, LastName and Email.

I want to clean the table User_Info by deleting redundant user (having the same triplet: FirstName, LastName and Email (not null and not empty)) and to update the column User_UID in Session table to the corresponding user_uid remaining in the User_Info table and pointing to the same user.

These are my tables:

  CREATE TABLE user_info (
    User_UID uniqueidentifier ROWGUIDCOL NULL DEFAULT (newid()),
    FirstName varchar(50),
    LastName varchar(50),
    Email varchar(50)
)

INSERT user_info VALUES(newid(), 'Marc', 'Jalloul', '[email protected]')
INSERT user_info VALUES(newid(), 'Marc', 'Jalloul', '[email protected]')
INSERT user_info VALUES(newid(), 'Estelle', 'Jalloul', '[email protected]')
INSERT user_info VALUES(newid(), 'Estelle', 'Jalloul', '[email protected]')
INSERT user_info VALUES(newid(), 'Tony', 'Jalloul', '[email protected]')
INSERT user_info VALUES(newid(), 'Tony', 'Jalloul', '[email protected]')
INSERT user_info VALUES(newid(), '', 'Mirebeau', '[email protected]')
INSERT user_info VALUES(newid(), '', 'Mirebeau', '[email protected]')
INSERT user_info VALUES(newid(), NULL, 'Mirebeau', '[email protected]')
INSERT user_info VALUES(newid(), NULL, 'Mirebeau', '[email protected]')

CREATE TABLE session (
    session_id INT IDENTITY(1, 1),
    User_UID char(40)
)

INSERT Session

SELECT User_UID FROM user_info

As expected result 2 rows should be deleted from User_info (row 2 and row 6 (or 1 and 5 depending on the order)) and the User_UID of row 2 and 6 of session table should be updated to User_UID of row 1 and 5 respectively.

This is my code To delete the redundant User from User_Info:

DELETE UI
FROM (
    SELECT *
        ,row_number() OVER (
            PARTITION BY FirstName
            ,LastName
            ,Email ORDER BY User_UID
            ) AS RowNum
    FROM user_info
    WHERE FirstName IS NOT NULL
        AND FirstName <> ''
        AND LastName IS NOT NULL
        AND LastName <> ''
        AND Email IS NOT NULL
        AND Email <> ''
    ) r
INNER JOIN user_Info UI
    ON r.User_UID = UI.User_UID
WHERE r.RowNum > 1

But I didn't find yet a way to update User_UID field of session table without using cursor

If anyone can help me and give me the code of this task if possible

Thanks

Upvotes: 1

Views: 122

Answers (1)

bummi
bummi

Reputation: 27377

You might use the OUTPUT clause to get the deleted rows and update session from the result, joining user_info.

Declare @tab Table (User_UID uniqueidentifier
               ,    FirstName varchar(50),
                    LastName varchar(50),
                    Email varchar(50))

DELETE UI
OUTPUT Deleted.* into @tab
FROM (
    SELECT *
        ,row_number() OVER (
            PARTITION BY FirstName
            ,LastName
            ,Email ORDER BY User_UID
            ) AS RowNum
    FROM user_info
    WHERE FirstName IS NOT NULL
        AND FirstName <> ''
        AND LastName IS NOT NULL
        AND LastName <> ''
        AND Email IS NOT NULL
        AND Email <> ''
    ) r
INNER JOIN user_Info UI
    ON r.User_UID = UI.User_UID
WHERE r.RowNum > 1



update Session 
set User_UID=u.User_UID
from @tab t
join user_info u on t.FirstName=u.FirstName and t.LastName=u.Lastname and t.Email=u.Email 
where t.User_UID=session.User_UID

Upvotes: 2

Related Questions