Solved

SQL Update on Column

Posted on 2014-03-19
10
255 Views
Last Modified: 2014-03-25
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
Comment
Question by:lburnsinmagnolia
10 Comments
 
LVL 15

Expert Comment

by:gplana
ID: 39939602
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
 
LVL 39

Expert Comment

by:lcohan
ID: 39939953
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
 

Author Comment

by:lburnsinmagnolia
ID: 39940363
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
 
LVL 15

Expert Comment

by:gplana
ID: 39940870
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
 

Author Comment

by:lburnsinmagnolia
ID: 39942248
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 40

Expert Comment

by:Sharath
ID: 39943484
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
 

Accepted Solution

by:
lburnsinmagnolia earned 0 total points
ID: 39943570
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39944131
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
 

Author Comment

by:lburnsinmagnolia
ID: 39944146
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
 

Author Closing Comment

by:lburnsinmagnolia
ID: 39952713
I found the solution and verified that it worked.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video discusses moving either the default database or any database to a new volume.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now