?
Solved

Updating my temp table

Posted on 2007-07-31
1
Medium Priority
?
157 Views
Last Modified: 2010-03-19
Hi,

I have an SP that puts the output in temp table. The output is as follows:

Date      Trn_Nr      T_Tips      Dept      Gross
7/12/2007      70177      1.07      1      2.1
7/12/2007      70177      1.07      1      2.1
7/12/2007      70177      1.07      1      2.1
7/12/2007      70177      1.07      1      2.1
7/12/2007      70177      1.07      1      1.5
7/12/2007      70177      1.07      1      1.5
7/12/2007      70177      1.07      1      2.3
7/12/2007      70177      1.07      1      2.3
7/12/2007      70177      1.07      1      0.9
7/12/2007      70177      1.07      1      0.9
7/12/2007      70178      0.88      1      10.5
7/12/2007      70178      0.88      1      2.3
7/12/2007      70178      0.88      1      0.9
7/12/2007      70178      0.88      1      0.9
7/12/2007      70179      0.36      1      1.5
7/12/2007      70179      0.36      1      2.1
7/12/2007      70180      0.09      1      1.5
7/12/2007      70181      0.63      1      2.1
7/12/2007      70181      0.63      1      2.1
7/12/2007      70181      0.63      1      2.1
7/12/2007      70182      2.22      1      2.9
7/12/2007      70182      2.22      1      2.9

Now what I need is that T_Tips should come once for each Trn_Nr and not in every row. So the first record for Trn_Nr 70177 should have value 1.07 and rest should be 0 for same Trn_Nr. Again for Trn_Nr 70718, the first record should have value 0.88 and the rest should have value 0 for same Trn_Nr. Since this data is in Temp table, can I fire one single query? If I use cursor and loop, it will be very time-consuming since we have nearly 80,000 records.

Regards,
MI
0
Comment
Question by:vj_mi
1 Comment
 
LVL 23

Accepted Solution

by:
adathelad earned 2000 total points
ID: 19599880
You really need an IDENTITY column in the temp table - it will make it a lot easier:

UPDATE t1
SET t1.T_Tips = 0
FROM #YourTempTable t1
    LEFT JOIN (SELECT Trn_Nr, MIN(NewidColumn) AS FirstId FROM #YourTempTable GROUP BY Trn_Nr) t2 ON t1.NewIdColumn = t2.FirstId
WHERE t2.Trn_Nr IS NULL
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
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.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

830 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