SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Using Group By in Update Statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

afarooqui
Starting Member

2 Posts

Posted - 04/08/2014 :  22:24:30  Show Profile  Reply with Quote
I recently had an interview and the interviewer has asked me to use a group by clause in an update statement. Here is the table and data:

Table name: Customer
cust Size Geo Segment
BP LG EU
Accenture LG US
MSC MED US
SclumbergerLG US

So the request is to update the Segment column with a unique number starting with number 1.
So for BP it should be 1, Accenture it should be 2, MSC it should be 3, Sclumberger should be 1 since the size and geo are not unique. He asked me to use a group by size and geo to generate a unique number for each combination and than make the update.

Help!

afarooqui
Starting Member

2 Posts

Posted - 04/09/2014 :  17:14:18  Show Profile  Reply with Quote
I wrote this query:
update Customer
set Segment= case
WHEN cust='Accenture' THEN '1'
WHEN cust='BP' THEN '2'
WHEN cust='MSC' THEN '3'
WHEN cust='Schlumberger THEN '1'
END;

and this one:
update Customer
set segment = CASE cust
WHEN 'Accenture' THEN '1'
WHEN 'BP' THEN '2'
WHEN 'MSC' THEN 3
WHEN 'Schlumberger' THEN '1'
END
WHERE cust IN ('Accenture', 'BP', 'MSC','Schlumberger')
;


However, my interviewer has asked me to try to group by size and geo to generate a unique number for each combination and than make the update. This solution would work for all values whereas the solution above only works for the specific values in the CASE statement.

Can someone help me with writing a group by for this request?
Go to Top of Page

kostya1122
Starting Member

8 Posts

Posted - 04/09/2014 :  18:53:30  Show Profile  Reply with Quote
something like this should work

update x
set x.segment = y.segment
from Customer as x
inner join ( select
size, geo,
row_number() over (order by size + geo) as segment
from Customer
group by size, geo) as y
on x.size = y.size and
x.geo = y.geo

Edited by - kostya1122 on 04/09/2014 18:54:24
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000