Author |
Topic |
dwi555
Starting Member
9 Posts |
Posted - 2013-08-20 : 00:40:46
|
i have this codei want to update nilai = total where kd_bulan = 9 and nilai = 0 where kd_bulan = 12anyone can help me??(sorry for bad english, and thanks before..) |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-08-20 : 02:06:10
|
Even though image which you posted is not clear...>>want to update nilai = total where kd_bulan = 9 and nilai = 0 where kd_bulan = 12UPDATE TableNameSET nilai = CASE WHEN kd_bulan = 9 THEN total WHEN kd_bulan = 12 THEN 0 ELSE nilai END --Chandu |
|
|
dwi555
Starting Member
9 Posts |
Posted - 2013-08-20 : 03:05:32
|
sorry for the picture,.,. this is the code select* from daskblnrtlwhere unitkey='120_'and mtgkey='960_'and idxdask='483_'and kd_bulan in (9,12)select sum(nilai) as total from daskblnrtlwhere unitkey='120_'and mtgkey='960_'and idxdask='483_'and kd_bulan in (9,12)actually this is what I meanI want to move the existing value in kd_bulan 12 to a value that is in kd_bulan 9, so that there is value in kd_bulan 9 increased and the value that is in kd_bulan 12 to be discharged |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-08-20 : 06:01:49
|
-- Try this;WITH CTE AS (select sum(nilai) total from daskblnrtl where unitkey='120_' and mtgkey='960_' and idxdask='483_' and kd_bulan in (9,12))UPDATE daskblnrtlSET nilai = (SELECT CASE WHEN kd_bulan = 9 THEN total WHEN kd_bulan = 12 THEN 0 END FROM CTE)WHERE unitkey='120_'and mtgkey='960_'and idxdask='483_' --Chandu |
|
|
dwi555
Starting Member
9 Posts |
Posted - 2013-08-20 : 23:27:41
|
thanks chandu the solution,.,., but there is an errorInvalid column name 'total'I use sql server 2008. I get advice from my friends to use the trigger, but I do not understand the trigger,, .., is there any function to temporarily store the value? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-08-21 : 00:41:57
|
do you mean this?update dset nilai = CASE kd_bulan WHEN 9 THEN total12 ELSE 0 ENDFROM (SELECT *,SUM(CASE WHEN kd_bulan = 12 THEN total END) OVER () AS total12FROM daskblnrtlwhere unitkey='120_'and mtgkey='960_'and idxdask='483_'and kd_bulan in (9,12))d ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
dwi555
Starting Member
9 Posts |
Posted - 2013-08-21 : 01:19:32
|
still the same errorInvalid column name 'total'. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-08-21 : 01:23:24
|
quote: Originally posted by dwi555 still the same errorInvalid column name 'total'.
Modified the query... Refer Edited by - bandi on 08/21/2013 00:53:56 post. Let me know further--Chandu |
|
|
dwi555
Starting Member
9 Posts |
Posted - 2013-08-21 : 01:42:59
|
quote: Originally posted by bandi -- Try this;WITH CTE AS (select sum(nilai) total from daskblnrtl where unitkey='120_' and mtgkey='960_' and idxdask='483_' and kd_bulan in (9,12))UPDATE daskblnrtlSET nilai = (SELECT CASE WHEN kd_bulan = 9 THEN total WHEN kd_bulan = 12 THEN 0 END FROM CTE)WHERE unitkey='120_'and mtgkey='960_'and idxdask='483_' --Chandu
thanks chandu,.,. results fit what I want, but there are fewer errors, ... I just want to change the existing value in kd_bulan 9 and 12.Here kd_bulan others turn into null. should only (2 row (s) affected)not (12 row (s) affected) |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-08-21 : 01:52:44
|
what should be the nilai for other kd_bulan values except 9,12...> ;WITH CTE AS (select sum(nilai) total from daskblnrtl where unitkey='120_' and mtgkey='960_' and idxdask='483_' and kd_bulan in (9,12))UPDATE daskblnrtlSET nilai = (SELECT CASE WHEN kd_bulan = 9 THEN total WHEN kd_bulan = 12 THEN 0 ELSE nilai END FROM CTE)WHERE unitkey='120_'and mtgkey='960_'and idxdask='483_'--Chandu |
|
|
dwi555
Starting Member
9 Posts |
Posted - 2013-08-21 : 02:00:45
|
quote: Originally posted by bandi what should be the nilai for other kd_bulan values except 9,12...> ;WITH CTE AS (select sum(nilai) total from daskblnrtl where unitkey='120_' and mtgkey='960_' and idxdask='483_' and kd_bulan in (9,12))UPDATE daskblnrtlSET nilai = (SELECT CASE WHEN kd_bulan = 9 THEN total WHEN kd_bulan = 12 THEN 0 ELSE nilai END FROM CTE)WHERE unitkey='120_'and mtgkey='960_'and idxdask='483_'--Chandu
thanks chandu,.,.,., the value is there and does not need to be changed.for kd_bulan it actually shows the date of the month. so there is 1 to 12, I just want to change the month 9 and month 12 |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-08-21 : 02:28:23
|
>>the value is there and does not need to be changedSET nilai = (SELECT CASE WHEN kd_bulan = 9 THEN total WHEN kd_bulan = 12 THEN 0 ELSE nilai END FROM CTE)Highlighted part is the fix for above requirement--Chandu |
|
|
dwi555
Starting Member
9 Posts |
Posted - 2013-08-21 : 02:57:43
|
quote: Originally posted by bandi >>the value is there and does not need to be changedSET nilai = (SELECT CASE WHEN kd_bulan = 9 THEN total WHEN kd_bulan = 12 THEN 0 ELSE nilai END FROM CTE)Highlighted part is the fix for above requirement--Chandu
cool,.,. you are indeed a master expert chandu,.,. Thanks very much, may God always bless you problem solved by chandu |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-08-21 : 07:41:43
|
quote: Originally posted by dwi555
quote: Originally posted by bandi >>the value is there and does not need to be changedSET nilai = (SELECT CASE WHEN kd_bulan = 9 THEN total WHEN kd_bulan = 12 THEN 0 ELSE nilai END FROM CTE)Highlighted part is the fix for above requirement--Chandu
cool,.,. you are indeed a master expert chandu,.,. Thanks very much, may God always bless you problem solved by chandu
Welcome --Chandu |
|
|
|