Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Selective SQL Table Update

Posted on 2011-03-16
9
Medium Priority
?
234 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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 500 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

610 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