Solved

Selective SQL Table Update

Posted on 2011-03-16
9
232 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 41

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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 41

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Backup & Restore 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.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

728 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