Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Using Group By in Update Statement

Author  Topic 

afarooqui
Starting Member

2 Posts

Posted - 2014-04-08 : 22:24:30
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 - 2014-04-09 : 17:14:18
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

15 Posts

Posted - 2014-04-09 : 18:53:30
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
Go to Top of Page
   

- Advertisement -