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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to update 2 records on 1 field

Author  Topic 

dwi555
Starting Member

9 Posts

Posted - 2013-08-20 : 00:40:46
i have this code



i want to update nilai = total where kd_bulan = 9 and nilai = 0 where kd_bulan = 12

anyone 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 = 12
UPDATE TableName
SET nilai = CASE WHEN kd_bulan = 9 THEN total
WHEN kd_bulan = 12 THEN 0
ELSE nilai
END



--
Chandu
Go to Top of Page

dwi555
Starting Member

9 Posts

Posted - 2013-08-20 : 03:05:32
sorry for the picture,.,.

this is the code

select* from daskblnrtl
where unitkey='120_'
and mtgkey='960_'
and idxdask='483_'
and kd_bulan in (9,12)

select sum(nilai) as total from daskblnrtl
where unitkey='120_'
and mtgkey='960_'
and idxdask='483_'
and kd_bulan in (9,12)

actually this is what I mean
I 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

Go to Top of Page

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 daskblnrtl
SET 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
Go to Top of Page

dwi555
Starting Member

9 Posts

Posted - 2013-08-20 : 23:27:41
thanks chandu the solution,.,., but there is an error

Invalid 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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-21 : 00:41:57
do you mean this?

update d
set nilai = CASE kd_bulan WHEN 9 THEN total12 ELSE 0 END
FROM (SELECT *,SUM(CASE WHEN kd_bulan = 12 THEN total END) OVER () AS total12
FROM daskblnrtl
where unitkey='120_'
and mtgkey='960_'
and idxdask='483_'
and kd_bulan in (9,12)
)d


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

dwi555
Starting Member

9 Posts

Posted - 2013-08-21 : 01:19:32
still the same error

Invalid column name 'total'.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-21 : 01:23:24
quote:
Originally posted by dwi555

still the same error

Invalid column name 'total'.


Modified the query... Refer Edited by - bandi on 08/21/2013 00:53:56 post. Let me know further

--
Chandu
Go to Top of Page

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 daskblnrtl
SET 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)
Go to Top of Page

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 daskblnrtl
SET 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
Go to Top of Page

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 daskblnrtl
SET 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
Go to Top of Page

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 changed
SET 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
Go to Top of Page

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 changed
SET 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
Go to Top of Page

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 changed
SET 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
Go to Top of Page
   

- Advertisement -