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


Query Help


Query Help

Author
Message
DBA12345
DBA12345
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 240
Hi I want to update Flag column in second table based on the Adder names.

If the Applicatiion has atleast one AIX and Adder name is UDB then the flag would be True.
If the Application has more the one AIX and Adder names are diferent then the flag would be null.


APpName OS Adder

App1 ||| Windows|||Null
App1 ||| Linux |||UDB
App1 ||| AIX |||UDB
App1 ||| Linux |||Sql

App2 ||| AIX ||| UDB
App2 ||| Windows||| UDB
App2 ||| Linux ||| UDB
App2 ||| AIX ||| UDB


OUTPUT SHOULD BE LOOK LIKE BELOW

APpName OS Adder Flag

App1||| Windows|||Null|||null
App1||| Linux |||UDB |||null
App1||| AIX |||UDB |||null
App1||| Linux |||Sql |||null

App2|||AIX ||| UDB|||TRUE
App2|||Windows||| UDB|||TRUE
App2|||Linux ||| UDB|||TRUE
App2|||AIX ||| UDB|||TRUE

Let me know fi you need addiitional information.

Thanks
Aswin
dwain.c
dwain.c
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16963 Visits: 6431
DBA12345 (9/11/2013)
Let me know fi you need addiitional information.


DDL and sample data in a consumable form would be nice.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5158 Visits: 2767
Please provide the DDL scripts for the table creation, ddl script to insert some dummy data and desired output...
Please follow the link in my signature and you will find answers to your question quickly :-P

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59716 Visits: 17947
DBA12345 (9/11/2013)


Hi I want to update Flag column in second table based on the Adder names.

If the Applicatiion has atleast one AIX and Adder name is UDB then the flag would be True.
If the Application has more the one AIX and Adder names are diferent then the flag would be null.


APpName OS Adder

App1 ||| Windows|||Null
App1 ||| Linux |||UDB
App1 ||| AIX |||UDB
App1 ||| Linux |||Sql

App2 ||| AIX ||| UDB
App2 ||| Windows||| UDB
App2 ||| Linux ||| UDB
App2 ||| AIX ||| UDB


OUTPUT SHOULD BE LOOK LIKE BELOW

APpName OS Adder Flag

App1||| Windows|||Null|||null
App1||| Linux |||UDB |||null
App1||| AIX |||UDB |||null
App1||| Linux |||Sql |||null

App2|||AIX ||| UDB|||TRUE
App2|||Windows||| UDB|||TRUE
App2|||Linux ||| UDB|||TRUE
App2|||AIX ||| UDB|||TRUE

Let me know fi you need addiitional information.

Thanks
Aswin



I am not sure which is correct, your explanation or your desired output. The stated rules, the sample data and the output you said you wanted don't match up.


If the Applicatiion has atleast one AIX and Adder name is UDB then the flag would be True.


In your output you have App1 as NULL but your rule says it should be "True".


If the Application has more the one AIX and Adder names are diferent then the flag would be null.


App2 has 2 rows for AIX but the Adder values are the same. According to the rules shouldn't this also be "True".

I cobbled together something resembling ddl and sample data in a format you should post with in the future.


create table #SomeTable
(
AppName char(4),
OS varchar(10),
Adder char(3)
)

insert #SomeTable
select 'App1', 'Windows', Null union all
select 'App1', 'Linux', 'UDB' union all
select 'App1', 'AIX ', 'UDB' union all
select 'App1', 'Linux', 'Sql' union all
select 'App2', 'AIX', 'UDB' union all
select 'App2', 'Windows', 'UDB' union all
select 'App2', 'Linux', 'UDB' union all
select 'App2', 'AIX', 'UDB'



Now to setup some data that meets the requirements of rule #2 I added some more data.


insert #SomeTable
select 'App3', 'AIX', 'UDB' union all
select 'App3', 'Windows', 'UDB' union all
select 'App3', 'Linux', 'UDB' union all
select 'App3', 'AIX', 'xxx'



OK. Now we have something to work with. If I understand your requirements I think you want something along these lines.


select *, case MyCount when 1 then 'True' end
from #SomeTable s
cross apply (select COUNT(distinct Adder) as MyCount from #SomeTable s2 where s2.AppName = s.AppName and s2.OS = 'AIX') x



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
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