View previous topic :: View next topic
|
Author |
Message |
senthamizh
New User
Joined: 27 Apr 2009 Posts: 23 Location: mumbai
|
|
|
|
Hi,
I have the below Table
Code: |
TABLE1,
REG CODE Class Amount
1 abc A 10
1 abc B 2
1 abc D 2
2 abc A 2
1 XYZ L 10 |
The requirement is to add matching class column Amount and update for REG 2 for Code = abc.
after update the table TABLE1 should look as below,
TABLE1
Code: |
REG CODE Class Amount
1 abc A 10
1 abc B 2
1 abc D 2
2 abc A 12
1 XYZ L 10 |
Can some one help me on this?
Thanks,
Sen
Code'd, for what it was worth |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
Use SQL to get the data from your table, use your program to play with your data, use SQL to update your table.
If you have large amounts of data and values to change you may have to unload the table and use the unloaded data then reload it. |
|
Back to top |
|
|
senthamizh
New User
Joined: 27 Apr 2009 Posts: 23 Location: mumbai
|
|
|
|
Is this possible in Spufi? with out using program. |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
Is this a production table? How many rows have to be changed? |
|
Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014 Posts: 446 Location: USA
|
|
|
|
Will it always be the REG '2' that needs to be updated i.e. the second occurrence of code and class combination? I assume there will Not be any REG 3, 4, 5 etc.
Will you have more code and class combination that needs update under a single REG Or multiple combinations need to be updated?
Please provide bigger sample of data.
. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Code: |
update table c
set c.Amount =
(select tbl1.aa
from table b,
(select b.Class bb , sum(b.Amount ) aa
from table b group by b.Class ) as tbl1
where b.REG= 2 and
tbl1.bb = b.Class )
where c.REG= 2 |
|
|
Back to top |
|
|
|