Замена одинаковых значений

Question

Необходимо сделать так, чтобы все имена одинаковые имена заменились над другие имена из другой таблицы( например, чтобы все Tim из таблицы Persons стали Mike из таблицы Full_Names, при этом чтобы другие, например Roberto не смогли стать Mike).

CREATE SCHEMA Pers;

DROP TABLE Pers.Persons

CREATE TABLE Pers.Persons(
   MemberID        int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
   FirstName        varchar(100) NULL,
   LastName        varchar(100) NOT NULL,
   Phone            varchar(12) NULL,
   Email            varchar(30) NOT NULL,
   Address         varchar(max) NOT NULL,
   INN             nvarchar(max) NOT NULL,
   Pasport_Series nvarchar(max) NOT NULL,
   Pasport_Number nvarchar(max) NOT NULL
   );

INSERT INTO Pers.Persons (FirstName, LastName, Phone, Email, Address,INN,Pasport_Series,Pasport_Number)
VALUES   
('Roberto', 'Tamburello', '555.123.4567', '[email protected]', 'Wall Street','120200','7115','612740'),  
('Janice', 'Galvin', '555.123.4568', '[email protected]', 'Soft Street','155900','2120','247123'),  
('Tim', 'Menon', '555.123.4570', '[email protected]', 'Milk Street','190250','2020','124467'),  
('Zheng', 'Mlap', '555.123.4569', '[email protected]', 'Clever Street','127340','1901','666666'),  
('Ron', 'Club', '555.123.4567', '[email protected]', 'Ball Street','120200','7115','612740'),  
('Jan', 'Gal', '555.123.4568', '[email protected]', 'Grant Street','155900','2120','247123'),  
('Tim', 'Mensons', '555.123.4570', '[email protected]', 'After Street','190250','2020','124467');

CREATE TABLE Pers.Full_Names(
   MemberID  int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
   FirstName varchar(100) NOT NULL,
   LastName varchar(100) NOT NULL,
   Address varchar(max) NOT NULL)

INSERT INTO Pers.Full_Names(FirstName,LastName,Address)
VALUES
('Andrew','Smith', 'LL Street'),
('Cole','Johnson','NN Street'),
('Nik','Dimond','SS Street'),
('Ryan', 'Gosling', 'QQ Street'),
('Mike','Jackson','ZZ Street'),
('Johnny','Depp','KK Street'),
('Sam','Mart','MM Street');

select * from pers.Full_Names
drop table pers.Full_Names

--Имя(рандомное имя из Persons)
DECLARE @ID as INT;
set @ID=1;
WHILE(@ID<=(Select Count(1) FROM Pers.Persons))
begin
   IF (
   UPDATE Pers.Persons 
   SET FirstName=(Select FirstName FROM Pers.Full_Names WHERE MemberID=@ID),
   LastName=(Select top 1 LastName FROM Pers.Persons ORDER BY NEWID() DESC),
   Address=(Select top 1 Address FROM Pers.Persons ORDER BY NEWID()),
   SET INN=HASHBYTES('SHA2_256',INN),
   Pasport_Series=HASHBYTES('SHA1',Pasport_Series),
   Pasport_Number=HASHBYTES('SHA2_512',Pasport_Number)
   WHERE MemberID=@ID;
   SET @ID=@ID+1;
end

БЛОГ НА HUSL

Замена одинаковых значений

Ответы (0 шт):