DECLARE @table TABLE
(
	Z_Status VARCHAR(10), 
	Z_Linked_Account INT, 
	EV870_ACCT_CODE INT, 
	EV870_NAME VARCHAR(500),
	Fuzzy_name VARCHAR(500),
	EV870_Company_Name VARCHAR(500),
	EV870_Email_Address VARCHAR(500),
	Country CHAR(3),
	Key1 VARCHAR(500),
	Key2 VARCHAR(500),
	New_Account INT
)
INSERT INTO @table VALUES
('SURVIVOR',27092,27093,'Abes, Jake','ABESJAKE','Det Norske Veritas','jake.abes@dnv.com','CAN','ABESJAKE jake.abes@dnv.com CAN','ABESJAKE jake.abes@dnv.com',null),
('SURVIVOR',12350,13972,'Abid, Ridha','ABIDRIDHA','OPSENS','ridha.abid@opsens.com','CAN','ABIDRIDHA ridha.abid@opsens.com CAN','ABIDRIDHA ridha.abid@opsens.com',null),
('SURVIVOR',12394,32648,'Abraham, Jennifer','ABRAHAMJENNIFER','Honeywell Process Solutions','jennifer.abraham@honeywell.com','USA','ABRAHAMJENNIFER jennifer.abraham@honeywell.com USA','ABRAHAMJENNIFER jennifer.abraham@honeywell.com',null),
('SURVIVOR',13385,16410,'Abraham, Kurt','ABRAHAMKURT','Gulf Publishing Company','kurt.abraham@worldoil.com','USA','ABRAHAMKURT kurt.abraham@worldoil.com USA','ABRAHAMKURT kurt.abraham@worldoil.com',null),
('SURVIVOR',11126,21300,'Abrams, Adriane','ABRAMSADRIANE','VaporTech Energy Services Inc.','aabrams@vaportechinc,com','CAN','ABRAMSADRIANE aabrams@vaportechinc,com CAN','ABRAMSADRIANE aabrams@vaportechinc,com',null),
('SURVIVOR',10996,17945,'Accadia, Mike','ACCADIAMIKE','Phoenix Contact Ltd.','maccadia@phoenixcontact.ca','CAN','ACCADIAMIKE maccadia@phoenixcontact.ca CAN','ACCADIAMIKE maccadia@phoenixcontact.ca',null),
('DUP',10996,14398,'Accadia, Mike','ACCADIAMIKE','Phoenix Contact Ltd.','maccadia@phoenixcontact.ca','CAN','ACCADIAMIKE maccadia@phoenixcontact.ca CAN','ACCADIAMIKE maccadia@phoenixcontact.ca',null),
('SURVIVOR',10298,32526,'Accounting, Emily','ACCOUNTINGEMILY','CanTorque Inc.','accounting@cantorque.com','CAN','ACCOUNTINGEMILY accounting@cantorque.com CAN','ACCOUNTINGEMILY accounting@cantorque.com',null),
('SURVIVOR',26870,26872,'Acc Payable, Eileen','ACCPAYABLEEILEEN','Power Plant Supply Co.',null,'CAN','ACCPAYABLEEILEEN  CAN','ACCPAYABLEEILEEN',null),
('SURVIVOR',30473,30480,'Acevedo, Enrique','ACEVEDOENRIQUE','Correcol S.A.','eacevedo@correcol.com','COL','ACEVEDOENRIQUE eacevedo@correcol.com COL','ACEVEDOENRIQUE eacevedo@correcol.com',null),
('SURVIVOR',23081,23157,'Acheampong, Nicole','ACHEAMPONGNICOLE','Aerotek','nacheamp@aerotek.com','USA','ACHEAMPONGNICOLE nacheamp@aerotek.com USA','ACHEAMPONGNICOLE nacheamp@aerotek.com',null),
('SURVIVOR',10129,23147,'Acheson, Harry','ACHESONHARRY','TISI Canada Inc.','HAcheson@teamindustrialservices.com','CAN','ACHESONHARRY HAcheson@teamindustrialservices.com CAN','ACHESONHARRY HAcheson@teamindustrialservices.com',null),
('DUP',13285,13954,'Address, Mailing','ADDRESSMAILING','PETROBRAS',NULL,'BRZ','ADDRESSMAILING  BRZ','ADDRESSMAILING',null),
('SURVIVOR',10919,15507,'Address, Mailing','ADDRESSMAILING','PETROBRAS',NULL,'BRZ','ADDRESSMAILING  BRZ','ADDRESSMAILING',null),
('SURVIVOR',34530,34531,'Abdulla, Feroz','ABDULLAFEROZ','Metalcraft Technology Inc.','feroz@metalcraft.ca','CAN','ABDULLAFEROZ feroz@metalcraft.ca CAN','ABDULLAFEROZ feroz@metalcraft.ca',null),
('SURVIVOR',12027,18555,'Abel, Tim','ABELTIM','Roevin Technical People','tabel@roevin.ca','CAN','ABELTIM tabel@roevin.ca CAN','ABELTIM tabel@roevin.ca',null),
('DUP',11895,15083,'Ahner, Julie','AHNERJULIE','Tyco Thermal Controls','jahner@tycothermal.com','USA','AHNERJULIE jahner@tycothermal.com USA','AHNERJULIE jahner@tycothermal.com',null),
('SURVIVOR',10539,15536,'Ahner, Julie','AHNERJULIE','Tyco Thermal Controls','jahner@tycothermal.com','USA','AHNERJULIE jahner@tycothermal.com USA','AHNERJULIE jahner@tycothermal.com',null)
select * from @table

update
	t
set 
	New_Account = maxacctno
from
	@table t
inner join
(
	select 
		MAX(ev870_acct_code) AS maxacctno,
		ev870_name
	from
		@table
	group by
		ev870_name
) as Dev1
on
t.EV870_NAME = dev1.EV870_NAME

select * from @table