update help - SQL Server Forums

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

update help

Author

Topic

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-06-30 : 04:57:51

i have a table like

tabl_schedule

id tenor instno emi intrest prin
1 6 1 1000 1000 0
1 6 2 1000 1000 0
1 6 3 1000 1000 0
1 6 4 1000 1000 0
1 6 5 1000 1000 0
1 6 6 4000 1000 3000

now i like to update tenor column with 3 and required that only instno
remains 1,2,3 and other records will be deleted and the amount in emi and prin column is
updated like below

id tenor instno emi intrest prin
1 6 1 1000 1000 0
1 6 2 1000 1000 0
1 6 3 4000 1000 3000

pls help

challenge everything

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-30 : 13:44:59

Can you explain the rule that you are using? Seems like you kept the first two rows, and then then changed the instno of the last row to 3. Is that the rule? "Keep first two rows and change the isntno of the last row to 3" Or is it something else?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-01 : 00:58:58

[code]
;with cte
as
(
select id, tenor, instno, emi, intrest, prin
from
(
select row_number() over (partition by id, tenor order by instno desc) as rn,*
from tabl_schedule
)t
where rn=1
)

update t
set t.emi = c.emi,
t.prin = c.prin
from tabl_schedule t
inner join cte c
on c.id = t.id
and c.tenor = t.tenor
where t.instno = 3
and c.instno > 3

delete from tabl_schedule where instno > 3
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources