Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jayram11
Yak Posting Veteran
97 Posts |
 Posted - 2011-09-22 : 17:37:18
|
| hi, i have following query and i finding it hard to change the select to update. i want to update PR_TERM_DATE with TERM_DATE for the result of this queryselect coalesce((select min(t2.PR_EFF_DATE) - 1 from IPF_PR t2 where t2.PR_NO = t1.PR_NO and t2.PR_EFF_DATE > t1.PR_EFF_DATE), t1.PR_TERM_DATE) as TERM_DATE, t1.*from IPF_PR t1where t1.PR_TERM_DATE <> coalesce((select min(t2.PR_EFF_DATE) - 1 from IPF_PR t2 where t2.PR_NO = t1.PR_NO and t2.PR_EFF_DATE > t1.PR_EFF_DATE), t1.PR_TERM_DATE) i have a longer way to do it but i want to know if it can be done in an update statement |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-22 : 23:03:53
|
| [code]UPDATE t1SET t1..PR_TERM_DATE=coalesce((select min(t2.PR_EFF_DATE) - 1 from IPF_PR t2 where t2.PR_NO = t1.PR_NOand t2.PR_EFF_DATE > t1.PR_EFF_DATE), t1.PR_TERM_DATE) from IPF_PR t1where t1.PR_TERM_DATE <> coalesce((select min(t2.PR_EFF_DATE) - 1 from IPF_PR t2 where t2.PR_NO = t1.PR_NOand t2.PR_EFF_DATE > t1.PR_EFF_DATE), t1.PR_TERM_DATE) [/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
jayram11
Yak Posting Veteran
97 Posts |
Posted - 2011-09-23 : 09:55:24
|
How come i did not see that  ....Thanks Visakh |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-23 : 10:29:59
|
| wc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
How to turn select to update