update records based on condition

Question

update records based on condition

riya_dave

Hall of Fame

Points: 3106
More actions
August 1, 2013 at 12:48 pm

#276539

hi
i have table , which can have millions of rows.
Member primarycondition primaryintensity secondarycondition secondaryintensity
M2345 hf 1
M2345 COPD 1
M2345 CAD 1
M2345 dia 1
M2345 Ast 1
M2345 hf 2
M2345 COPD 2
M2345 CAD 2
M2345 dia 2
M2345 Ast 2
what i need to do is if member has more than 1 condition, then
i need to make 1 as a primary and another one secondary condition. with same primary intensity
based on this order
hf
COPD
CAD
dia
Ast
so the result will be
Member primarycondition primaryintensity secondarycondition secondaryIntensity
M2345 hf 1
M2345 COPD 1
M2345 CAD 1
M2345 dia 1
M2345 Ast 1
M2345 hf 2
M2345 COPD 2
M2345 CAD 2
M2345 dia 2
M2345 Ast 2
i need to do it for all member,if member has more than 1 primary condition.
any help?

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

I have tried to help you on so many threads and once again you haven't posted any details. If you would actually post ddl and sample data along with the desired output you will find that you receive answers a LOT faster and with a lot less posts. Please do yourself, and everyone on these forums a favor, if you are going to post asking for help then please make the effort to post ddl and sample data. Take a few minutes and read the first link in my signature.

_______________________________________________________________

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

riya_dave Hall of Fame Points: 3106 More actions · Answer 2

this is my DDL

create table member

(memberid int,

primarycondition varchar(50),

PrimaryIntensity int,

secondarycondition varchar(50),

Secondaryintensioty int)

sample data i already provided in my previous thread

Lowell SSC Guru Points: 323518 More actions · Answer 3

you DID NOT post readily consumable sample data.

your sample headers had five columns, but the data only appears in three.

how is anyone supposed to be able to use that?

i ran what you posted thru my macro and came up with this:

;With MySampleData([Member],[primarycondition],[primaryintensity],[secondarycondition],[secondaryintensity])

AS

(

SELECT 'M2345','hf','1' UNION ALL

SELECT 'M2345','COPD','1' UNION ALL

SELECT 'M2345','CAD','1' UNION ALL

SELECT 'M2345','dia','1' UNION ALL

SELECT 'M2345','Ast','1' UNION ALL

SELECT 'M2345','hf','2' UNION ALL

SELECT 'M2345','COPD','2' UNION ALL

SELECT 'M2345','CAD','2' UNION ALL

SELECT 'M2345','dia','2' UNION ALL

SELECT 'M2345','Ast','2'

)

SELECT * FROM MySampleData

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

riya_dave (8/1/2013)
this is my DDL
create table member
(memberid int,
primarycondition varchar(50),
PrimaryIntensity int,
secondarycondition varchar(50),
Secondaryintensioty int)
sample data i already provided in my previous thread

I am not a paid employee of this site. I do not want to go digging around through all your posts to find the information that you should be providing. I say this because I truly want to help, but you don't seem willing to put in even as much effort to a solution to your problems as you are expecting of other to do. I know that there a number of people around these forums who see certain posters names and simply skip on to the next one because they know the post is not going to have the details to offer much help without a lot of effort. Your name is certainly on that list. You need to make an effort at making it easy on the folks who are offering their advice free of charge.

_______________________________________________________________

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Lowell (8/1/2013)
you DID NOT post readily consumable sample data.
your sample headers had five columns, but the data only appears in three.
how is anyone supposed to be able to use that?
i ran what you posted thru my macro and came up with this:
;With MySampleData([Member],[primarycondition],[primaryintensity],[secondarycondition],[secondaryintensity])
AS
(
SELECT 'M2345','hf','1' UNION ALL
SELECT 'M2345','COPD','1' UNION ALL
SELECT 'M2345','CAD','1' UNION ALL
SELECT 'M2345','dia','1' UNION ALL
SELECT 'M2345','Ast','1' UNION ALL
SELECT 'M2345','hf','2' UNION ALL
SELECT 'M2345','COPD','2' UNION ALL
SELECT 'M2345','CAD','2' UNION ALL
SELECT 'M2345','dia','2' UNION ALL
SELECT 'M2345','Ast','2'
)
SELECT * FROM MySampleData

You might also notice the "data" matches exactly what is stated as desired output. 😉

_______________________________________________________________

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

riya_dave Hall of Fame Points: 3106 More actions · Answer 6

my desire out put will be

Member primarycondition primaryintensity secondarycondition secondaryIntensity

M2345 hf 1

M2345 COPD 1

M2345 CAD 1

M2345 dia 1

M2345 Ast 1

M2345 hf 2

M2345 COPD 2

M2345 CAD 2

M2345 dia 2

M2345 Ast 2

it not taking spaces

but hf and associated primaryintensity will be under primary condition and primaryintensity

other will goes under secondarycondition and secondary intensity

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

riya_dave (8/1/2013)
my desire out put will be
Member primarycondition primaryintensity secondarycondition secondaryIntensity
M2345 hf 1
M2345 COPD 1
M2345 CAD 1
M2345 dia 1
M2345 Ast 1
M2345 hf 2
M2345 COPD 2
M2345 CAD 2
M2345 dia 2
M2345 Ast 2

Since that matches exactly to what you said your data is like I guess you could select the rows from your table. Nothing changed between your table data and your output.

_______________________________________________________________

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

Here you go, it generates the expected output based on your sample data.

;With MySampleData([Member],[primarycondition],[primaryintensity],[secondarycondition],[secondaryintensity])

AS

(

SELECT 'M2345','hf','1','','' UNION ALL

SELECT 'M2345','COPD','1','','' UNION ALL

SELECT 'M2345','CAD','1','','' UNION ALL

SELECT 'M2345','dia','1','','' UNION ALL

SELECT 'M2345','Ast','1','','' UNION ALL

SELECT 'M2345','hf','2','','' UNION ALL

SELECT 'M2345','COPD','2','','' UNION ALL

SELECT 'M2345','CAD','2','','' UNION ALL

SELECT 'M2345','dia','2','','' UNION ALL

SELECT 'M2345','Ast','2','',''

)

SELECT * FROM MySampleData

By the way, I couldn't use your DDL because M2345 is not an int.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

riya_dave Hall of Fame Points: 3106 More actions · Answer 9

thisis not the result that i want

i have five PRIMARY condition ,

hf,

COPD,

CAD,

dia,

ast

primary intensity could be 1,2, 3

now i want to check suppose if i have all 5 condition with same primary Intensity,

HF and that intensity should be under primarycondition and primaryintensity

other 4 should be move to secondarycondition and secondaryintensity

and for that condition primarycondition and primaryintensity should update to null,

now suppose i have just 3 primary condition , CAD,dia and ast with same intenstty

then CAD and associated intensity should be primarycondition and primaryIntensity

and dia and ast should be move to secondarycondition and secondaryIntensity

Lowell SSC Guru Points: 323518 More actions · Answer 10

I think you will need to update the table against a version of itself featuring row_number().

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

riya_dave Hall of Fame Points: 3106 More actions · Answer 11

but i need loop or condtion hwo to do it

example if primarycondition = 'HF'

then update table a

set secondary condtion = null

if there is no primary condition = 'HF'

then

if primarycondition = 'CAD'

update table a

set secondarycondition = primarycondition

except for primarycondition ='CAD'

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

riya_dave (8/1/2013)
but i need loop or condtion hwo to do it
example if primarycondition = 'HF'
then update table a
set secondary condtion = null
if there is no primary condition = 'HF'
then
if primarycondition = 'CAD'
update table a
set secondarycondition = primarycondition
except for primarycondition ='CAD'

Use ROW_NUMBER.

If you need specific help with syntax you MUST post ddl and sample data in a consumable format. If you can do that you will get tested and accurate code back very quickly. If you continue to refuse to help us to help you, you will continue to get answers with no real details.

You should read this article and think about how this relates to what you have posted in this thread.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

_______________________________________________________________

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

riya_dave Hall of Fame Points: 3106 More actions · Answer 13

i used row_number ,but how to get multiple condition in update

for example if condition = 'HF'

then update

else if condition ='AST'

then update

Lowell SSC Guru Points: 323518 More actions · Answer 14

UPDATE SomeTable

SET Column =

CASE

WHEN condition = 'HF'

THEN SomeCalculationOrValueOrColumn

WHEN condition2 = 'AST'

THEN ADifferentCalculationOrValueOrColumn

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!