April 11, 2009 at 5:41 am
Dear bro and sis,
I have a problem to update data from table.
The Source table looks like this:
PrimaryKey1 PrimaryKey2 TitleOrder Title TitleDescription
1 1 0 Movies A great movies
1 1 1 NULL Starring : BigStar
1 1 2 NULL Guest : Guess
1 1 3 Music Pop
1 1 4 NULL Rock
1 1 5 NULL Reggae
I want to update where columns Title IS NULL.
For TitleOrder 1 and 2, I want to update Title column with the value of "Movies"
For TitleOrder 4 and 5, I want to update Title column with the value of "Music"
Thanx for any suggestion or links that may help me 🙂
April 11, 2009 at 6:47 am
bambang (4/11/2009)
Dear bro and sis,I have a problem to update data from table.
The Source table looks like this:
PrimaryKey1 PrimaryKey2 TitleOrder Title TitleDescription
1 1 0 Movies A great movies
1 1 1 NULL Starring : BigStar
1 1 2 NULL Guest : Guess
1 1 3 Music Pop
1 1 4 NULL Rock
1 1 5 NULL Reggae
I want to update where columns Title IS NULL.
For TitleOrder 1 and 2, I want to update Title column with the value of "Movies"
For TitleOrder 4 and 5, I want to update Title column with the value of "Music"
Thanx for any suggestion or links that may help me 🙂
There seems to be something "wrong" in your sample data.
What's the relationship between PrimaryKey1 PrimaryKey2 TitleOrder ?
How about...
(proprietary code)
Update Tsub
set Title = THead.Title
from MyMovies Tsub
inner join MyMovies THead
on THead.PrimaryKey1 = Tsub.PrimaryKey1
and THead.PrimaryKey2 = Tsub.PrimaryKey2
and THead.TitleOrder = 0
and Tsub.TitleOrder > 0
where Tsub.Title is null
You could determine all headers sections with their min(TitleOrder) + next(titleorder)
and then update the rows between each of the sections with the title of the min(Titleorder)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 11, 2009 at 10:07 am
Dear Bro ALZDBA,
Thanx for your suggestion, but it's not work yet.
The query result would be like this :
PrimaryKey1 PrimaryKey2 TitleOrder Title TitleDescription
1 1 0 Movies A great movies
1 1 1 Movies Starring : BigStar
1 1 2 Movies Guest : Guess
1 1 3 Music Pop
1 1 4 Movies Rock
1 1 5 Movies Reggae
The value for column Title in TitleOrder 4 and 5 should be "Music", not "Movies".
Please, any other suggestion?
I'm having difficulties in searching solution in google and msdn because I don't know the keyword for this kind of problem 😛
April 11, 2009 at 11:09 am
bambang (4/11/2009)
Dear Bro ALZDBA,Thanx for your suggestion, but it's not work yet.
The query result would be like this :
PrimaryKey1 PrimaryKey2 TitleOrder Title TitleDescription
1 1 0 Movies A great movies
1 1 1 Movies Starring : BigStar
1 1 2 Movies Guest : Guess
1 1 3 Music Pop
1 1 4 Movies Rock
1 1 5 Movies Reggae
The value for column Title in TitleOrder 4 and 5 should be "Music", not "Movies".
Please, any other suggestion?
I'm having difficulties in searching solution in google and msdn because I don't know the keyword for this kind of problem 😛
Indeed, that's way my first question was posted in the reply.
Apparently your titleorder column is a unique key in that table and a dangerous one because it contains dependency with other rows !!
Sort out your keys and column relationships and rework that solution !
Try this.
declare @SSC table (PrimaryKey1 int not null, PrimaryKey2 int not null, TitleOrder int not null, Title varchar(128) NULL, TitleDescription varchar(128) NULL )
set nocount on
Insert into @SSC
Select 1, 1, 0, 'Movies', 'A great movies'
union all
Select 1, 1, 1, NULL ,'Starring : BigStar'
union all
Select 1, 1, 2, NULL, 'Guest : Guess'
union all
Select 1, 1, 3, 'Music', 'Pop'
union all
Select 1, 1, 4, NULL, 'Rock'
union all
Select 1, 1, 5, NULL, 'Reggae'
union all
Select 1, 1, 6, 'Candy', 'lollyPop'
union all
Select 1, 1, 7, NULL, 'Rock'
union all
Select 1, 1, 8, NULL, 'sugar'
select *
from @SSC
Select T1.TitleOrder, T1.Title, min(T2.TitleOrder) as NextTitleOrder
from @SSC T1
left join @SSC T2
On T1.PrimaryKey1 = T2.PrimaryKey1
and T1.PrimaryKey2 = T2.PrimaryKey2
and T1.TitleOrder < T2.TitleOrder
and T2.Title is not null
Where T1.Title is not null
group by T1.TitleOrder, T1.Title
order by TitleOrder
;
Update Tsub
set Title = THead.Title
from @SSC Tsub
inner join ( Select T1.PrimaryKey1, T1.PrimaryKey2, T1.TitleOrder, T1.Title, min(T2.TitleOrder) as NextTitleOrder
from @SSC T1
left join @SSC T2
On T1.PrimaryKey1 = T2.PrimaryKey1
and T1.PrimaryKey2 = T2.PrimaryKey2
and T1.TitleOrder < T2.TitleOrder
and T2.Title is not null
Where T1.Title is not null
group by T1.PrimaryKey1, T1.PrimaryKey2, T1.TitleOrder, T1.Title ) THead
on Tsub.PrimaryKey1 = THead.PrimaryKey1
and Tsub.PrimaryKey2 = THead.PrimaryKey2
and Tsub.TitleOrder > THead.TitleOrder
and ( Tsub.TitleOrder < THead.NextTitleOrder
or THead.NextTitleOrder is null )
Select *
from @SSC
order by PrimaryKey1, PrimaryKey2, TitleOrder
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 11, 2009 at 12:09 pm
I must confess, I don't approve of your schema either.
However, this will give you the result you're looking for.
declare @test-2 table (PK1 int, pk2 int, TitleOrder int, Title varchar(20), TitleDescription varchar(20))
insert into @test-2
select 1, 1, 0, 'Movies', 'A great movies' union all
select 1, 1, 1, NULL, 'Starring : BigStar' union all
select 1, 1, 2, NULL, 'Guest : Guess' union all
select 1, 1, 3,'Music', 'Pop' union all
select 1, 1, 4, NULL, 'Rock' union all
select 1, 1, 5, NULL, 'Reggae'
select * from @test-2
select pk1,pk2,titleorder,ISNULL(title,lastTitle),titledescription
from @test-2 t1
outer apply (select top 1 t2.Title as lastTitle
from @test-2 t2
where t2.Title is not null
and t2.TitleOrder < t1.TitleOrder
order by titleOrder desc) f
order by Titleorder
__________________________________________________
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 13, 2009 at 3:44 am
great solution Bob.
Nice application of outer apply. Still have to get used to that feature 😎
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 13, 2009 at 7:44 am
Thanks, AL. 🙂
There is a lot of new (to me) stuff out there that is worth practicing with. I don't really "own" a technique until I've used it enough to have it occur to me when looking at a problem. I'm making a point to do that with CROSS APPLY, OUTER APPLY, inline table-valued functions, ROW_NUMBER(), etc etc.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply