By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Player (
ID int,
Email varchar(100),
FirstName varchar(100));
insert into Player values
(1,'Bob.Lance@gmail.com','Bob'),
(2,'Laura.Davis@gmail.com','Laura'),
(3,'Josh.Stevens@gmail.com','Josh'),
(4,'Alex.Wild@gmail.com','Alex'),
(5,'Bob.Frank@gmail.com','Bob'),
(6,'Alex.Summers@gmail.com','Alex');
create table Sport (
Sport varchar(100),
Players varchar(100));
insert into Sport values
('Golf','Laura'),
('Basketball','Josh'),
('Baseball','Alex'),
('Football','Bob'),
('Volleyball','Laura'),
('Swimming','Alex'),
('Driving','Bob'),
('Boxing','Bob');
14 rows affected
SELECT
MIN(Email) AS Email
FROM Player
GROUP BY
FirstName
HAVING
COUNT(DISTINCT Email) = 1;
| Josh.Stevens@gmail.com |
| Laura.Davis@gmail.com |
WITH uniqueMails AS
(SELECT
FirstName,
MIN(Email) AS Email
FROM Player
GROUP BY
FirstName
HAVING
COUNT(DISTINCT Email) = 1)
SELECT * FROM uniqueMails;
| FirstName | |
|---|---|
| Josh | Josh.Stevens@gmail.com |
| Laura | Laura.Davis@gmail.com |
WITH uniqueMails AS
(SELECT
FirstName,
MIN(Email) AS Email
FROM Player
GROUP BY
FirstName
HAVING
COUNT(DISTINCT Email) = 1)
UPDATE s
SET s.Players = u.Email
FROM Sport s
JOIN uniqueMails u
ON s.Players = u.FirstName;
3 rows affected
SELECT * FROM Sport;
| Sport | Players |
|---|---|
| Golf | Laura.Davis@gmail.com |
| Basketball | Josh.Stevens@gmail.com |
| Baseball | Alex |
| Football | Bob |
| Volleyball | Laura.Davis@gmail.com |
| Swimming | Alex |
| Driving | Bob |
| Boxing | Bob |