Solved

SQL Update on Column

Posted on 2014-03-19
10
265 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 40

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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
 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL BULK INSERT Comma Delimited Issue 8 79
Please help with the below query - SQL Server 11 29
sql 2016 Integration Service connecting to 2012 3 33
TSQL Challenge... 7 37
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

820 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