By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,374 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Updating distinct rows

P: 32
Hi,

Assuming I have a table called 'Combined' with three attributes.

Combined
id - Case_Num - Ticket_Count

I need to make an update query so that for every distinct Case_Num, write a "1" in the Ticket_Count. I've tried a few different things using DISTINCT and DISTINCTROWS but I'm getting nowhere!

This seems very simple, but I can't seem to get it to work. Any help would be greatly appreciated.

J
Mar 5 '08 #1
Share this Question
Share on Google+
7 Replies


P: 32
I've tried the following:

UPDATE Combined SET Combined.Ticket_Count = "1"
WHERE (SELECT DISTINCT Combined.Case_Num FROM Combined;);

I know this doesn't work, but I'm grasping at straws trying to get something to work. Should I make the SELECT query first, and then make the UPDATE query using that SELECT query somehow?

Thanks again,
Jason
Mar 6 '08 #2

Expert Mod 2.5K+
P: 2,545
Hi,

Assuming I have a table called 'Combined' with three attributes.

Combined
id - Case_Num - Ticket_Count

I need to make an update query so that for every distinct Case_Num, write a "1" in the Ticket_Count. I've tried a few different things using DISTINCT and DISTINCTROWS but I'm getting nowhere!

This seems very simple, but I can't seem to get it to work. Any help would be greatly appreciated.

J
Hi. I'm sorry but it's not at all clear what you are trying to achieve here. For each row there appears to be a unique ID field, and a non-unique case number. It would really help if you posted some example rows to show what is in your table.

Whatever you do to group or select case numbers, if grouping by case number is the only criterion you apply an update will not differentiate further and you will end up with ALL of the Ticket_Count entries in your table updated to 1. As that can't be what you are trying to achieve, there must be something else involved. Is it that you want to set the Ticket Count on the first occurrence of each different case number to 1, but leave the others at some other value?

It would be really helpful if you could include in your next post a small sample of what the table contains now and what you want to update it to so we can help you more.

-Stewart
Mar 6 '08 #3

P: 32
Hi Stewart,

Sorry if my description was unclear. It's as you said: "set the Ticket Count on the first occurrence of each different case number to 1" and leave the others blank or "0" would even suffice.

So here is a sample of the current table, with ticket_count currently all empty.

Expand|Select|Wrap|Line Numbers
  1. Case_Num    Ticket_Count
  2. A3747422    
  3. A3747422    
  4. A3747422    
  5. A3747422    
  6. A3747422    
  7. A3747422    
  8. A3841537    
  9. A3841537    
  10. A3887216    
  11. A3902765    
  12. A3933409    
  13. A3933409    
  14. A3933409    
  15. A3933409    
  16. A3933409    
I want the end result to look like this:


Expand|Select|Wrap|Line Numbers
  1. Case_Num    Ticket_Count
  2. A3747422    1
  3. A3747422    
  4. A3747422    
  5. A3747422    
  6. A3747422    
  7. A3747422    
  8. A3841537    1
  9. A3841537    
  10. A3887216    1
  11. A3902765    1
  12. A3933409    1
  13. A3933409    
  14. A3933409    
  15. A3933409    
  16. A3933409
I hope that that's a little more clear. Thanks for your help!

Jason
Mar 6 '08 #4

Expert Mod 2.5K+
P: 2,545
...I want the end result to look like this:
Expand|Select|Wrap|Line Numbers
  1. Case_Num    Ticket_Count
  2. A3747422    1
  3. A3747422    
  4. A3747422    
  5. A3747422    
  6. A3747422    
  7. A3747422    
  8. A3841537    1
  9. A3841537    
  10. A3887216    1
  11. A3902765    1
  12. A3933409    1
  13. A3933409    
  14. A3933409    
  15. A3933409    
  16. A3933409
Hi Jason. This is much clearer, thanks. Your ID field isn't shown in the table - is it a number? If it is, it is then possible to select the lowest ID row for each case number. The SQL for updating would then be:
Expand|Select|Wrap|Line Numbers
  1. UPDATE [yourtable] SET [yourtable].[Ticket_Count] = 1
  2. WHERE ((([yourtable].[ID]) In (SELECT Min([yourtable].[ID]) AS MinID
  3. FROM [yourtable]
  4. GROUP BY [yourtable].[Case_Num];)));
Replace [yourtable] with the real name of your table.

On your test data with added semi-random numeric IDs this results in:
Expand|Select|Wrap|Line Numbers
  1. ID Case Number Ticket
  2.  1 A3747422     1
  3.  3 A3747422     
  4.  9 A3747422     
  5. 10 A3747422     
  6. 12 A3747422     
  7. 15 A3747422     
  8.  7 A3841537     1
  9.  8 A3841537     
  10.  6 A3887216     1
  11.  4 A3902765     1
  12.  2 A3933409     1
  13.  5 A3933409     
  14. 11 A3933409     
  15. 13 A3933409     
  16. 14 A3933409     
Try this out and see how it goes for you.

-Stewart
Mar 6 '08 #5

P: 32
Hi Stewart,

Thanks for your quick reply! This looks good and I can see how it works. I ran this on my test database and this worked perfectly. The only problem is that a colleague of mine exports this table to excel every day for his reports and removed the ID attribute from the live database as he said it interfered somehow. (which makes no sense to me)

Long story short, I have no ID attribute to use. I hate to trouble you further, but are there any alternatives with just those two original attributes?

Thanks again,
Jason
Mar 6 '08 #6

Expert Mod 2.5K+
P: 2,545
Hi Stewart,

Thanks for your quick reply! This looks good and I can see how it works. I ran this on my test database and this worked perfectly. The only problem is that a colleague of mine exports this table to excel every day for his reports and removed the ID attribute from the live database as he said it interfered somehow. (which makes no sense to me)

Long story short, I have no ID attribute to use. I hate to trouble you further, but are there any alternatives with just those two original attributes?

Thanks again,
Jason
Hi Jason. There are no alternatives that would make any sense - without the ID the table breaks a cardinal rule of database design which is that each row stored must be unique. If rows are not unique there is no meaningful way to distinguish between different entries in the table, and in your case all you have is a list of case numbers with some duplicates for reasons unknown.

I think you should tackle the actual problem - the removal of the ID - in order to move this onwards.

Sorry I can't be of more assistance.

-Stewart
Mar 6 '08 #7

P: 32
Hi Stewart,

Thank you so much for all your help. I think you're right about the whole situation. I'll make adjustments and I'm sure my colleague will find a way to work through it.

Thanks again, I really appreciate it!

Jason
Mar 6 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.