Solved

Selective SQL Table Update

Posted on 2011-03-16
9
229 Views
Last Modified: 2012-05-11

How can I remove all but 1 tot amt from each cust group in table below:

Cust          Tot          Detail
1                10               3
1                10               5
1                10               2
2                18               4
2                18              14

 
I want table to look like this:

Cust          Tot          Detail
1                10               3
1                                   5
1                                   2
2                18               4
2                                 14

0
Comment
Question by:thayduck
  • 4
  • 3
  • 2
9 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 35153811
try like this.
;with cte as (
select *,ROW_NUMBER() over (partition by Cust order by Tot) rn
  from your_table)
select Cust,case rn when 1 then Tot else null end Tot,Detail
  from cte

Open in new window

0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35156206
Actually, I don't think you want the table to look like that.  You may want a report to have its output to look like that, though.

What you may want is to have the table's Tot column contain a runing total, so that it looks more like:
Cust          Tot          Detail
1                10               3
1                  7               5
1                  2               2
2                18               4
2                14             14 

Open in new window


There is no gurantee that any given query will return the rows in any specific order.  If you remove the Tot from all but one row, you could wind up with a query result that looks something like:

Cust          Tot          Detail
1                                   2
1                10               3
1                                   5
2                18               4
2                                 14 

Open in new window


or


Cust          Tot          Detail
1                                   5
1                10               3
1                                   2
2                                 14 
2                18               4

Open in new window

0
 

Author Comment

by:thayduck
ID: 35156705
I want to update that same table not create a new table.
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 22

Expert Comment

by:8080_Diver
ID: 35157642
Okay, I'll accept that you have your reasons.

How are you generating the order of the rows?  (I.e. is there some sort of ORDER BY that you are not showing?)

And, for that matter, what happens when you add another row, say a new row for Cust 1 with a Detail of 4?  If you set all except of the one rows' Tot columns to NULL excet for the one you indicate, how are you going to deal with that new row and it's Tot?

The code that Sharath_123 provided in response # 35153811 may work and, then again, the second time you run it, especially after adding a new row, it may wipe of the only Tot and leave all of the rows with Tot set to NULL.
0
 
LVL 40

Accepted Solution

by:
Sharath earned 125 total points
ID: 35160494
update statement.
;with cte as (
select *,ROW_NUMBER() over (partition by Cust order by Tot) rn
  from your_table)
update cte
   set Tot = case rn when 1 then Tot else null end

Open in new window

0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35160634
Sharath_123,

What happens after changes slightly and the order of the rows comes back as:

Cust          Tot          Detail
1                                   5
1                10               3
1                                   2
2                                 14 
2                18               4

Open in new window


instead of

Cust          Tot          Detail
1                10               3
1                                   5
1                                   2
2                18               4
2                                 14 

Open in new window


Your query will result in the following:

Cust          Tot          Detail
1                                   5
1                                   3
1                                   2
2                                 14 
2                                   4

Open in new window


0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35160643
@!thayduck,

Are there more columns in that table (e.g. a date of entry column) that could be used to force an ORDER BY?
0
 

Author Comment

by:thayduck
ID: 35164824
This is working for me right now:

--make sure only 1 os6monamt appears per cust and processdate in #accthist for later calc purpose
      UPDATE T1
      SET    os6monamt = 0
      FROM   #accthist T1,
             #accthist T2
       WHERE  Substring(t1.custrss, 1, 6) = Substring(t2.custrss, 1, 6)    
       and T1.id > T2.id

But will look into Sharath 123 solution to.                  
                       
0
 

Author Closing Comment

by:thayduck
ID: 35224684
Thanks, it worked.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

803 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