Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

update records based on condition Expand / Collapse
Author
Message
Posted Friday, August 02, 2013 1:26 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
riya_dave (8/2/2013)
but i dont need select ,i just need update statement, the store proc should not have select


So turn the select into an update.

;with SortedData as
(
select cc.*, s.SortOrder, ROW_NUMBER() over(partition by ID order by s.SortOrder) as RowNum
from CustomCode cc
left join #SortSomething s on cc.pcondition = s.pcondition
)

update SortedData
set pcondition = case when RowNum = 1 AND SortOrder is not null then pcondition else null end,
Pinten = case when RowNum = 1 AND SortOrder is not null then Pinten else null end,
scondition = case when RowNum > 1 then pcondition
when RowNum = 1 AND SortOrder is null then scondition
else null end ,
sinten = case when RowNum > 1 then Pinten
when RowNum = 1 AND SortOrder is null then sinten
else null end



_______________________________________________________________

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 Moden's 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)
Post #1480585
Posted Friday, August 02, 2013 1:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 04, 2013 8:13 AM
Points: 190, Visits: 367
u r updating cte i need to update customecode table
Post #1480593
Posted Friday, August 02, 2013 2:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 04, 2013 8:13 AM
Points: 190, Visits: 367
this code is not working

it snot necessary that all 5 condition will exists for that particular ID, there can be 3 or 4 ,not all 5

in that case it has to go with order
Post #1480595
Posted Friday, August 02, 2013 2:35 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 04, 2013 8:13 AM
Points: 190, Visits: 367
IF YOU EXECUTING IT SECOND TIME it moves all the values to scondition

also if there is duplicate example

id pcondition scondition
1 sa null
1 sa null

it should not do anything coz its same pcondition , but the code moves it too
Post #1480610
Posted Friday, August 02, 2013 2:35 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 12,744, Visits: 31,078
my best guess; not sure what you expect on the last record, it doesn't seem to follow the right pattern, but at elast with a framework like this, oyu could modify it to fix that edge case:
;WITH RequiredOrder 
AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY MyTarget.ID ORDER BY CASE WHEN pcondition='AF' THEN 1
WHEN pcondition='CA' THEN 2
WHEN pcondition='CAD' THEN 3
WHEN pcondition='AST' THEN 4
WHEN pcondition='HF' THEN 5
ELSE 6
END) AS RW,
MyTarget.ID,
MyTarget.pcondition,
CASE
WHEN pcondition='AF' THEN 1
WHEN pcondition='CA' THEN 2
WHEN pcondition='CAD' THEN 3
WHEN pcondition='AST' THEN 4
WHEN pcondition='HF' THEN 5
ELSE 6
END AS Indicator,
MyTarget.Pinten,
MyTarget.scondition,
MyTarget.sinten
FROM CustomCode MyTarget)

SELECT
RW,
ID,
CASE WHEN RW = 1 THEN pcondition ELSE NULL END AS Newpcondition,
CASE WHEN RW = 1 THEN Pinten ELSE NULL END AS NewPinten,
CASE WHEN RW = 1 THEN NULL ELSE scondition END AS Newscondition,
CASE WHEN RW = 1 THEN NULL ELSE sinten END AS Newsinten
FROM RequiredOrder ORDER BY ID,RW



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1480611
Posted Friday, August 02, 2013 2:47 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
riya_dave (8/2/2013)
u r updating cte i need to update customecode table


Did you actually notice what happens when you do that? It updates the base table.


_______________________________________________________________

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 Moden's 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)
Post #1480616
Posted Friday, August 02, 2013 2:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
riya_dave (8/2/2013)
IF YOU EXECUTING IT SECOND TIME it moves all the values to scondition

also if there is duplicate example

id pcondition scondition
1 sa null
1 sa null

it should not do anything coz its same pcondition , but the code moves it too


Are there anymore rules that you have not yet mentioned? This is like coding against a moving target.


_______________________________________________________________

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 Moden's 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)
Post #1480617
Posted Friday, August 02, 2013 2:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 04, 2013 8:13 AM
Points: 190, Visits: 367
no there is no more rule, but if you execute is second time it moves everything to scondition

and also there may be duplicate values
Post #1480621
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse