• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

SQL Update on Column

I have a table with columns named Rent and RentBalance

I need an update query that will update the rentbalance to be the rent column value subtracted from the rentbalance from the previous row. As you can see from below, I eliminated a specific transactioncode, as that is the start point for rentbalance.

example:

One of my attempts (epic fail!)

update t
            set RentBalance = rentbalance - (select top 1 t2.rent
              from TRANSACTIONS t2
            where T2.STOREID = t.STOREID
                  AND T2.CUSTOMERID = t.CUSTOMERID
                  AND T2.AGREEMENTID = t.AGREEMENTID
                  AND T2.TransactionDate >= t.TransactionDate)
      from #temptrans t
            where t.TransactionCode <> @PURCHASECODE

Any help?

Thanks
0
lburnsinmagnolia
Asked:
lburnsinmagnolia
1 Solution
 
gplanaCommented:
I think the syntax of your query is not correct, because UPDATE statement doesn't have FROM clause. The syntax of UPDATE sentence is:

UPDATE table
   SET field1=value1, field2=value2, ...
WHERE condition

Can you explain which is your structure in the database? Which tables do you have, which fields on every table, what foreign keys, ...
0
 
lcohanDatabase AnalystCommented:
You must match the temptrans with the table ALIAS name t so the records matching the key will be updated so would be something like:

update #temptrans
set RentBalance = t.rentbalance - (select top 1 t2.rent
                                                            from TRANSACTIONS t2
                                                            where T2.STOREID = t.STOREID
                                                                  AND T2.CUSTOMERID = t.CUSTOMERID
                                                                  AND T2.AGREEMENTID = t.AGREEMENTID
                                                                  AND T2.TransactionDate >= t.TransactionDate)
from #temptrans t
where  #temptrans.STOREID = t.STOREID
      AND #temptrans.CUSTOMERID = t.CUSTOMERID
      AND #temptrans.AGREEMENTID = t.AGREEMENTID
      AND t.TransactionCode <> @PURCHASECODE
0
 
lburnsinmagnoliaAuthor Commented:
here is the #temptrans table creation code.
      select TransactionID
      , transactioncode
      , STOREID
      , CustomerID
      , AgreementID
      , TransactionDate
      , LeaseChargeBalance
      , LeaseCharges
      , originventorycost
      , InventoryCostBalance
      , RentBalance
      , Rent
      into #temptrans
from TRANSACTIONS
      where AgreementID <> 0
              order by STOREID, CustomerID, AgreementID, TransactionDate

Then I go thru the temp table to update different columns.

The update syntax is correct.  I am updating other columns with different queries.

example:

update t
            set LeaseChargeBalance = (select coalesce(sum(t2.LeaseCharges), 0)
              from TRANSACTIONS t2
            where T2.STOREID = t.STOREID
                  AND T2.CUSTOMERID = t.CUSTOMERID
                  AND T2.AGREEMENTID = t.AGREEMENTID
                  AND T2.TransactionDate <= t.TransactionDate)
      from #temptrans t

That update works perfectly.  It's just the new one which updates differently that I have the problem with.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
gplanaCommented:
Then I don't understand, because the UPDATE syntax of Standard SQL is like I have exposed to you.

Have you tryied the inner query alone (changing the t.xxx fields by hard coded values)? Does it work?
Have you tryied the update sentence without the inner query? Does it work?
0
 
lburnsinmagnoliaAuthor Commented:
Ok,


Once again, it is not a syntax problem.  It updates!  It is just updating incorrectly.  
I need the rentbalance to be a running total from a start point to an end point for each agreement in the table.

I could do it with a CURSOR but with over 4 million rows, that would take forever.  I was hoping someone would know how to achieve the update without having to resort to a CURSOR.
0
 
SharathData EngineerCommented:
Can you post some sample date from #temptrans  table and the expected result? I understand that you want a running total in RentBalance column but if you can explain with some data, that would be helpful.
0
 
lburnsinmagnoliaAuthor Commented:
I have fixed the problem.  With some google searching, I found the solution.

Here is what I had to do to make a running total:  note the rows unbounded preceding in the select.  That is what I found out to make a column a running total

with cte1 as
(select top 200 t1.transactionid
       ,t1.transactiondate
       ,t1.rent
         ,t1.rentbalance
         ,(select sum(coalesce(rentbalance, 0)) from transactions t2 where t1.AgreementID = t2.AgreementID and t1.CustomerID = t2.CustomerID and t2.TransactionCode = 101) as rBal
       ,sum(t1.rent) over(partition by t1.customerid, t1.agreementid order by t1.customerid, t1.agreementid, t1.transactiondate rows unbounded preceding) as runningtotal
from transactions t1
where t1.TransactionCode in (902, 101)
and t1.AgreementID <> 0
order by transactiondate, TransactionCode
)

insert into @rtable
select transactionid
       ,rent
       ,rbal + runningtotal
         , runningtotal
from cte1
0
 
Anthony PerkinsCommented:
Running totals are always complicated and in my view should not be kept in the database, but rather let the presentation layer handle it.  Having said that, SQL Server 2012 (and now 2014) have good windowing functions that make this a lot easier and you are not faced with performance nightmares when you have a large table.
0
 
lburnsinmagnoliaAuthor Commented:
Anthony,

The column is actually a decrementing value with a start point (rentbalance) and as new records are added with a rent amount (rent), the rent balance is calculated to be the rent balance from the prior transaction - the rent of the newly added row.  So once the historical data is implemented (my problem here),  It will merely be calculated as the value from the last record - the rent from the new one.  

I agree with your statement that a calculated running total would not be a good solution at all and would take a lot of resources depending on the size of table.  I could have done this easily with a cursor, but with over 4 mil records, that would have taken too long.
0
 
lburnsinmagnoliaAuthor Commented:
I found the solution and verified that it worked.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now