Smartway update statement

Question

Post reply

Smartway update statement

asita

SSCarpal Tunnel

Points: 4144
More actions
January 24, 2016 at 9:22 pm

#399618

Hello
Can you please let me know the smart way of doing this? (below three statements into simple one statement) please help me
UPDATE dbo.ClnTrail
SET trailproposed_Weekno = '01 - 01/01/2016' where trailproposed_Weekno = '54 - 01/01/2016'
UPDATE dbo.ClnTrail
SET trailStart_WeekNo = '01 - 01/01/2016' where trailStart_WeekNo = '54 - 01/01/2016'
UPDATE dbo.ClnTrail
SET trailEnd_Weekno = '01 - 01/01/2016' where trailEnd_Weekno = '54 - 01/01/2016'
---- I tried Below
UPDATE dbo.ClnTrail
SET
trailproposed_Weekno = Case when trailproposed_Weekno ='54 - 01/01/2016' then '01 - 01/01/2016' else trailproposed_Weekno END,
trailStart_WeekNo = Case when trailStart_WeekNo ='54 - 01/01/2016' then '01 - 01/01/2016' else trailStart_WeekNo END,
trailEnd_Weekno = Case when trailEnd_Weekno ='54 - 01/01/2016' then '01 - 01/01/2016' else trailEnd_Weekno END,
Thank you a ton
Asita

Viewing 7 posts - 1 through 6 (of 6 total)

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

matak SSCrazy Points: 2674 More actions · Answer 1

Why do you want to combine them into one statement ?

They are probably better off staying separate.

asita SSCarpal Tunnel Points: 4144 More actions · Answer 2

Actually checking myself one thing ,

also the second one is, I have this kind of individual queries 10, I just put 3 here, so would like to do it in a single line

Thank you

Asita

nm.rajesh SSC-Addicted Points: 421 More actions · Answer 3

Hi Asita ,

what is the benefit you are getting out of it can you be more specific please , the update statement using a case statement would hamper the performance as it is not having a where clause to restrict the Update.

Thanks,

Raj

asita SSCarpal Tunnel Points: 4144 More actions · Answer 4

Raj, Thank you

as I mentioned I have same kind of sql statements 10 simple update I gave 3 above,

so I am checking one update statement rather than 10 update statements in update statement

please help me out with it

Thank you

Asita

ben.brugman SSChampion Points: 13369 More actions · Answer 5

asita (1/24/2016)
Hello
Can you please let me know the smart way of doing this? (below three statements into simple one statement) please help me
UPDATE dbo.ClnTrail
SET trailproposed_Weekno = '01 - 01/01/2016' where trailproposed_Weekno = '54 - 01/01/2016'
UPDATE dbo.ClnTrail
SET trailStart_WeekNo = '01 - 01/01/2016' where trailStart_WeekNo = '54 - 01/01/2016'
UPDATE dbo.ClnTrail
SET trailEnd_Weekno = '01 - 01/01/2016' where trailEnd_Weekno = '54 - 01/01/2016'
---- I tried Below
UPDATE dbo.ClnTrail
SET
trailproposed_Weekno = Case when trailproposed_Weekno ='54 - 01/01/2016' then '01 - 01/01/2016' else trailproposed_Weekno END,
trailStart_WeekNo = Case when trailStart_WeekNo ='54 - 01/01/2016' then '01 - 01/01/2016' else trailStart_WeekNo END,
trailEnd_Weekno = Case when trailEnd_Weekno ='54 - 01/01/2016' then '01 - 01/01/2016' else trailEnd_Weekno END,
Thank you a ton
Asita

A solution could be, (but is very similar to your suggestion):

UPDATE dbo.ClnTrail

SET trailproposed_Weekno = replace (trailproposed_Weekno, '54 - 01/01/2016','01 - 01/01/2016'),

trailStart_WeekNo = replace (trailStart_WeekNo , '54 - 01/01/2016','01 - 01/01/2016'),

trailEnd_Weekno = replace (trailEnd_Weekno , '54 - 01/01/2016','01 - 01/01/2016')

Ben

asita SSCarpal Tunnel Points: 4144 More actions · Answer 6

Excellent old hank

it is worked great

Thank you all for your suppoet