April 29, 2010 at 9:17 am
I have a table similar to the one below. I want to update the message field in table1 for each record type.
RecID, RecType, Hierachy, Message
1, 5555, 10,
2, 5555, 20,
3, 5555, 30,
4, 6666, 10,
5, 6666, 20,
6, 7777, 10,
For each record that has the same RecType, set the message of the record with the lowest hierachy to be 'Y' and set all the others in the group to be 'N'. Similar to what I have shown below.
RecID, RecType, Hierachy, Message
1, 5555, 10, Y
2, 5555, 20, N
3, 5555, 30, N
4, 6666, 10, Y
5, 6666, 20, N
6, 7777, 10, Y
Can this be done with an Update statement?
Many Thanks
April 29, 2010 at 9:42 am
Yes. Here's one way.
Depending on the volume of rows that you have to update, there may be more performant solutions.
declare @sample table (RecID int, RecType int, Hierarchy int, Msg char(1))
insert into @sample
Values
(1, 5555, 10, null ),
(2, 5555, 20, null ),
(3, 5555, 30, null ),
(4, 6666, 10, null ),
(5, 6666, 20, null ),
(6, 7777, 10, null )
select * from @sample
update s
set s.Msg = case when s.Hierarchy = ca.minHierarchy then 'Y' else 'N' end
from @sample s
cross apply (select MIN(hierarchy) as minHierarchy from @sample s2 where s2.RecType = s.RecType) ca
select * from @sample
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 29, 2010 at 3:19 pm
Another method. (Thanks for the setup code Bob)
declare @sample table (RecID int, RecType int, Hierarchy int, Msg char(1))
insert into @sample
Values
(1, 5555, 10, null ),
(2, 5555, 20, null ),
(3, 5555, 30, null ),
(4, 6666, 10, null ),
(5, 6666, 20, null ),
(6, 7777, 10, null )
select * from @sample
;WITH c (RecType, MinH) AS (
SELECT RecType, MIN(Hierarchy) FROM @sample s2 GROUP BY RecType)
UPDATE S
SET Msg = CASE WHEN s.Hierarchy = C.MinH THEN 'Y' ELSE 'N' END
FROM @sample S
INNER JOIN C ON S.RecType = C.RecType
select * from @sample
April 29, 2010 at 3:26 pm
You're quite welcome, Seth. 😀
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 29, 2010 at 10:39 pm
Here is another method. Again , Thanks for the Setup BOB!
;WITH CTE(RecID , RecType , Hierarchy , Msg,R)
AS
(
select RecID , RecType , Hierarchy , Msg, ROW_NUMBER() OVER(PARTITION BY RecType ORDER BY Hierarchy ASC ) R from @sample )
UPDATE CTE
SET Msg = CASE WHEN R = 1 THEN 'Y'
ELSE 'N'
END
Hope this helps!
Cheers!
April 30, 2010 at 2:13 am
Many thanks for all the code
Very helpful thanks
April 30, 2010 at 11:13 am
You're welcome, Squidder.
Please take a moment to look at how I set up the problem. When you take the time to post CREATE TABLE statements or declare a table variable, and provide some INSERTs to create sample data (instead of just doing a cut-and-paste from a spreadsheet), then its easy for people to jump in and start coding and testing solutions.
It only took me a few minutes to set up, but you are the one asking for assistance, so you really ought to be the one to spend those few minutes. Please be aware that all of us here are unpaid volunteers and our time is as valuable to us as yours is to you.
You do get points for drawing us a picture of the expected results. For the people trying to answer your question, it beats the heck out of long, verbal descriptions of what you're trying to accomplish. 😀
In future posts, take time to set up your problem like I did. You will get faster responses that have been coded and tested. Best of luck to you.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 30, 2010 at 2:07 pm
-- Here is the setup:
declare @sample table (RecID int, RecType int, Hierarchy int, Msg char(1));
insert into @sample Values (1, 5555, 10, null );
insert into @sample Values (2, 5555, 20, null );
insert into @sample Values (3, 5555, 30, null );
insert into @sample Values (4, 6666, 10, null );
insert into @sample Values (5, 6666, 20, null );
insert into @sample Values (6, 7777, 10, null );
There are 2 ways to do this.
-- The first method assumes that the lowest Hierarchy in each RecType always = 10.
-- If that's the case, then you can do what you want with a single update.
-- This will be the most efficient method.
update @sample set Msg = case Hierarchy when 10 then 'Y' else 'N' end;
-- If there is no guarantee about the lowest Hierarchy always = 10, then
-- you can use the following update statement, which joins a sub-query
-- returning the lowest Hierarchy for each RecType.
update A
set A.Msg = case when a.Hierarchy = b.min_Hierarchy then 'Y' else 'N' end
from @sample A
join
(
select rectype, min(Hierarchy) min_Hierarchy
from @sample
group by rectype
) as B on B.rectype = A.rectype
;
Using either method, the end result is the same:
[font="Courier New"]
RecID RecType Hierarchy Msg
----------- ----------- ----------- ----
1 5555 10 Y
2 5555 20 N
3 5555 30 N
4 6666 10 Y
5 6666 20 N
6 7777 10 Y
[/font]
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 30, 2010 at 6:36 pm
squidder11 (4/30/2010)
Many thanks for all the codeVery helpful thanks
To help you out with what "The Dixie Flatline" is saying above, please see the article at the first link in my signature below. People will trip over each other to try to help you if you post data in a readily consumable format like in that article.
--Jeff Moden
May 1, 2010 at 1:31 am
again many thanks for everyone's help and also thanks for the pointers on code setup. I'll make sure I do this on all future posts.
May 2, 2010 at 5:49 am
You can never have too many solutions. This one is very short:
UPDATE INLINE SET OldMsg = Msg
FROM (SELECT OldMsg = Msg, Msg = CASE WHEN Hierarchy = MIN(Hierarchy) OVER (PARTITION BY RecType) THEN 'Y' ELSE 'N' END FROM @Sample) INLINE;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 4, 2010 at 2:31 am
Hi Guys ,
Please excuse if the solution already exists... My first reply in SQL Central FORUMS..
Create Table TT(RecID int, RecType int, Hierachy smallint, Message char(1))
GO
Insert into TT(RecID, RecType, Hierachy)
Values(1, 5555, 10),
(2, 5555, 20),
(3, 5555, 30),
(4, 6666, 10),
(5, 6666, 20),
(6, 7777, 10)
Select * from TT
Go
WITH myTT(RecID,RowID,Message)
AS
(
SELECT RecID, ROW_NUMBER() OVER(PARTITION BY RecType ORDER BY Hierachy) AS RowID,Message
FROM TT
)
UPDATE myTT SET Message = CASE WHEN ROWID= 1 THEN 'Y' ELSE 'N' END
Go
Select * from TT
Drop Table TT
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply