SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to update 2 records on 1 field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dwi555
Starting Member

Indonesia
9 Posts

Posted - 08/20/2013 :  00:40:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2169 Posts

Posted - 08/20/2013 :  02:06:10  Show Profile  Reply with Quote
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

Indonesia
9 Posts

Posted - 08/20/2013 :  03:05:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2169 Posts

Posted - 08/20/2013 :  06:01:49  Show Profile  Reply with Quote
-- 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

Edited by - bandi on 08/21/2013 00:53:56
Go to Top of Page

dwi555
Starting Member

Indonesia
9 Posts

Posted - 08/20/2013 :  23:27:41  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 08/21/2013 :  00:41:57  Show Profile  Reply with Quote
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

Indonesia
9 Posts

Posted - 08/21/2013 :  01:19:32  Show Profile  Reply with Quote
still the same error

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

bandi
Flowing Fount of Yak Knowledge

India
2169 Posts

Posted - 08/21/2013 :  01:23:24  Show Profile  Reply with Quote
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

Indonesia
9 Posts

Posted - 08/21/2013 :  01:42:59  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2169 Posts

Posted - 08/21/2013 :  01:52:44  Show Profile  Reply with Quote
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

Indonesia
9 Posts

Posted - 08/21/2013 :  02:00:45  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2169 Posts

Posted - 08/21/2013 :  02:28:23  Show Profile  Reply with Quote
>>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

Indonesia
9 Posts

Posted - 08/21/2013 :  02:57:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2169 Posts

Posted - 08/21/2013 :  07:41:43  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000