Selective SQL Table Update


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

thayduckProgrammer AnalystAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
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
 
SharathData EngineerCommented:
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
 
8080_DiverCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

 
thayduckProgrammer AnalystAuthor Commented:
I want to update that same table not create a new table.
0
 
8080_DiverCommented:
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
 
8080_DiverCommented:
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
 
8080_DiverCommented:
@!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
 
thayduckProgrammer AnalystAuthor Commented:
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
 
thayduckProgrammer AnalystAuthor Commented:
Thanks, it worked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.