Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update Column from Another Column - Plz check attachment Expand / Collapse
Author
Message
Posted Wednesday, September 12, 2012 3:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 26, 2012 5:27 AM
Points: 6, Visits: 22
Hello Experts,

I have a table with a column called Z_Status: the values of this column are DUP and Survivor, which means one of the record is Duplicate and another is survivor.

Now i have to update last Column called New_Account from Column C (EV870_ACCT_CODE), the New_Account Column should only be updated by the Max(EV870_ACCT_CODE) when grouping the information by Key2 Column.

I have shown the output and highlited the result in Green background (please check the attachment).

Apologize if i have not explained it well.

Waiting for the suggestions

Cheers


Ish



  Post Attachments 
Sample.xlsx (9 views, 12.00 KB)
Post #1358228
Posted Thursday, September 13, 2012 2:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 26, 2012 5:27 AM
Points: 6, Visits: 22
please help
Post #1358389
Posted Thursday, September 13, 2012 2:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:45 AM
Points: 5,221, Visits: 5,120
You will be better off posting data in a consumable format in order to get help quickly, please see the second link in my signature for help on this

I have done this for you below and also given 1 solution, others may chip in now that they can easily recreate the problem on their setups.

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





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1358410
Posted Thursday, September 13, 2012 5:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 26, 2012 5:27 AM
Points: 6, Visits: 22
Hello Anthony,

It works perfectly, thank you so much
Post #1358477
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse