Update query using a MIN() function

Question

Hello, I need to take the amount in a field in one table and add it to a field in another table. Normally that wouldn't be that difficult but I am trying to do this in mass for multiple results. I was able to do this as an INSERT INTO creating a new line item but now I am being asked to update an existing line item instead. The table I'm trying to UPDATE/INSERT INTO has multiple line items with the primary keys being BLDG, LEAS, PERIOD, TYPE. My insert query looked like this:

insert into FASB  (bldgid, leasid, period, inccat, fasbtype, amount) select a.bldgid, b.newleasid, (min(a.period)-1) as period, a.inccat, a.fasbtype, b.fasbadjamt*(-1) from FASB as a, tb_cm_fasbadjamnt as b where a.bldgid=b.bldgid and b.newleasid=a.leasid and a.inccat=b.inccat and a.fasbtype='G'  group by  a.bldgid, b.newleasid, a.inccat, a.fasbtype, b.fasbadjamt

I now need to add b.fasbadjamt*(-1) to a.amount for the MIN(PERIOD) line for each BLDG,LEAS combo. 

I can find the line that I need to update by running this

select  a.bldgid, b.newleasid,  a.inccat, a.fasbtype, min(a.period) as period from FASB as a, tb_cm_fasbadjamnt as b where a.bldgid=b.bldgid and b.newleasid=a.leasid and a.inccat=b.inccat and a.fasbtype='G'  and b.fasbadjamt<>0   group by  a.bldgid, b.newleasid, a.inccat, a.fasbtype

I tried this update but it only gave me 4 results (the four leases that all equal the min(period)of the entire table instead of the min(period) per lease

update FASB as a, tb_cm_fasbadjamnt as b set a.amount=a.amount+b.fasbadjamt  where a.bldgid=b.bldgid and b.newleasid=a.leasid and a.inccat=b.inccat and a.fasbtype='G'  and b.fasbadjamt<>0 and a.period=(select min(period) from fasb as a, tb_cm_fasbadjamnt as b where a.bldgid=b.bldgid and b.newleasid=a.leasid and a.inccat=b.inccat and a.fasbtype='G')

Is there anyway to update the min(period) line per lease? thanks

Answer 1

If you're using SQL 2005 and up, try

;with cte as (select *, row_number() over (partition by Bldg, Leas order by Period) as Rn from FASB)

update a set Amount = a.Amount + b.FasBadjAmt

from cte inner join tb_cmd_fasBadjAmnt b on A.BldgId = B.BldgId

and ...

where a.Rn = 1

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

This is minimal polite behavior on SQL forums. 

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. 

Please Google the cardinality problems with the 1970's Sybase UPDATE.. FROM.. Naomi gave you. It does not work and with this useless narrative, there is a good chance it will kill you. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL