Solved

Selective SQL Table Update

Posted on 2011-03-16
9
231 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

733 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