Friday, January 04, 2013 8:25 PM
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
Friday, January 04, 2013 8:46 PMModerator
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
where a.Rn = 1
For every expert, there is an equal and opposite expert. - Becker's Law
- Proposed As Answer by Eric Isaacs Friday, January 04, 2013 10:09 PM
- Marked As Answer by Kalman TothMicrosoft Community Contributor, Moderator Wednesday, January 09, 2013 6:18 PM
Friday, January 04, 2013 9:50 PM
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