CODEWITHSUNDEEP
sqlsql-server
gymcode
gymcode

Reputation: 4623

SQL INSERT Statement By Comparing and Retrieving Data Involving Multiple Columns

+----+------------------+------------------+--------------+
| ID | EmployeeUsername |  EmployeeEmail   | ManagerEmail |
+----+------------------+------------------+--------------+
|  1 | Ron_UN           |   [email protected]   | [email protected] |
|  2 | Pat_UN           |   [email protected]   |              |    Pat is Ron's Manager.
|  3 | Amy_UN           |   [email protected]   | [email protected] |   
|  4 | Tom_UN           |   [email protected]   |              |    Tom is Amy's Manager.
+----+------------------+------------------+--------------+

I would like to insert a new column ManagerUsername.

+----+------------------+------------------+--------------+-----------------+
| ID | EmployeeUsername |  EmployeeEmail   | ManagerEmail | ManagerUsername | <--
+----+------------------+------------------+--------------+-----------------+
|  1 | Ron_UN           | [email protected]     | [email protected] |                 |
|  2 | Pat_UN           | [email protected]     |              |                 |
|  3 | Amy_UN           | [email protected]     | [email protected] |                 |
|  4 | Tom_UN           | [email protected]     |              |                 |
+----+------------------+------------------+--------------+-----------------+

May I know if there is a statement which I can insert into ManagerUsername column if ManagerEmail belongs to an existing Employee?

+----+------------------+------------------+--------------+-----------------+
| ID | EmployeeUsername |  EmployeeEmail   | ManagerEmail | ManagerUsername |
+----+------------------+------------------+--------------+-----------------+
|  1 | Ron_UN           | [email protected]     | [email protected] | Pat_UN <--      |
|  2 | Pat_UN           | [email protected]     |              |                 |
|  3 | Amy_UN           | [email protected]     | [email protected] | Tom_UN <--      |
|  4 | Tom_UN           | [email protected]     |              |                 |
+----+------------------+------------------+--------------+-----------------+

I am currently working out the logic of this query, therefore I do not have a SQL statement yet. I will update when I am able to form one.

P.S. Using Microsoft SQL Server Management Studio

Upvotes: 1

Views: 33

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

We can do this with an update self join:

UPDATE a
SET ManagerUsername = b.EmployeeUsername;
FROM yourTable a
INNER JOIN yourTable b
    ON a.ManagerEmail = b.EmployeeEmail

This assumes that you've already created a new column ManagerUsername in your table. If not, then you'll have to do that first before running the above update. Note that the update does precisely what you intend, because employee records having no manager email would not self join to anything (probably because they are manager records), and so the manager username for those records would not even be in scope during the update, and the values would remain NULL.

Upvotes: 4

Related Questions