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


update records based on condition


update records based on condition

Author
Message
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27002 Visits: 17557
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 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)
riya_dave
riya_dave
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 367
u r updating cte i need to update customecode table
riya_dave
riya_dave
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 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
riya_dave
riya_dave
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 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
Lowell
Lowell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29246 Visits: 39985
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

--
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
Sean Lange
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27002 Visits: 17557
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 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)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27002 Visits: 17557
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 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)
riya_dave
riya_dave
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 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
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