Updating 1 Record When There Are Duplicates

[szeiss]

I have a table called rpt_sp_category with the following columns: sponsor, dept_cd, subproject_id, fao_id, fund, agency, orgn, cnt_fund_groups, avail_appr, expenses, spending_plan

My problem is the rpt_sp_category will have duplicate records where the cnt_fund_groups > 1, but I only want to update one of those records with the spending_plan value. This table
is not static, a procedure will truncate the table and rebuild it on the fly. I've googled for examples, but so far nothing I've tried is working.

[update rpt_sp_category sc
set spending_plan = (
SELECT SUM(D2.AMOUNT) Tot_Amount
FROM tbl.faos F3,
tbl.spend_plan_data D2,
tbl.spend_plan P2,
tbl.SPEND_PLAN_VERSION_TYPE v,
tbl.subprojects S3
WHERE v.linetype_cd = 'B'
AND F3.fao_id = D2.fao_id
AND P2.version_type_id = v.version_type_id
AND D2.spend_plan_id = P2.spend_plan_id
AND P2.subproject_id = S3.subproject_id
AND D2.fy_id = 2019
AND D2.sp_rpt_cd = '0000'
AND D2.sp_obj_cd = '0000'
AND D2.fm_id = 0
AND S3.subproject_closed = 'N'
AND rowid in (select min(rowid)
from rpt_spendplan_category
where rpt_spendplan_category.FAO_ID = f3.fao_id
group by FAO_ID
order by fao_id))]

Thanks for any help!

 

[Andrzejek]

Do you have a Primary Key field in your table?


---- Andy

There is a great need for a sarcasm font.

 

[szeiss]

No, I don't. It's a report table where the data can change each time it's ran. That's why I thought of using rowid.

Thanks,
Sherry

 

[Andrzejek]

rowid is a work-around (I personally hate to do that), but you still have to know which record to update.

If you have 4 records with the same rpt_sp_category

[pre]
rpt_sp_category spending_plan ROWID
3 123BGFD567KJHK
3 1234.50 876DSFSDF89SD8
3 89778DFSDFSDF8
3 IUYDSF7SDF876D
[/pre]
What makes the second records 'special' to be updated?


---- Andy

There is a great need for a sarcasm font.

 

[szeiss]

Thank you, but we actually changed the query so it is working fine now using listagg.