SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update Column from Another Column - Plz check attachment


Update Column from Another Column - Plz check attachment

Author
Message
ishchopra
ishchopra
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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
Attachments
Sample.xlsx (22 views, 12.00 KB)
ishchopra
ishchopra
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 22
please help
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10406 Visits: 6378
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
When a question, really isn't a question - Jeff Smith
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


ishchopra
ishchopra
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 22
Hello Anthony,

It works perfectly, thank you so much
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search