Solved

Help Needed With Query

Posted on 2015-01-17
5
53 Views
Last Modified: 2015-01-20
I need help with one more Query and then I'm done.  For this one, I need to update the SinceLastAssigned value for every participant who has a value of zero to the MAX value in that column, plus one (in the case of the data presented, that would be 176).  Please let me know if you have any questions.  Appreciate the help!

Sample Data
0
Comment
Question by:penlandt
[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
  • 2
  • 2
5 Comments
 
LVL 69

Expert Comment

by:Qlemo
ID: 40556036
Is 176 the value for all rows having a zero?
0
 
LVL 1

Author Comment

by:penlandt
ID: 40556248
Yes, that's correct.   I'm assuming that this can be done in one query but the first part (of course) has to be identifying the maximum value in the since last assigned column, then updating all the rows where the value is 0 to that maximum value plus one.  Hope that helps.
0
 
LVL 69

Accepted Solution

by:
Qlemo earned 500 total points
ID: 40556285
That's easy. If you would need unique values, that's getting difficult ;-).
update  tbl set sinceLastAssigned = nextMaxAssigned
from (select max(sinceLastAssigned)+1 as nextMaxAssigned from tbl) m
where sinceLastAssigned = 0

Open in new window

0
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40557191
Hi,

I agree with Qlemo,

You can Update
Update Tbl set sinceLastAssigned = (SELECT max(sinceLastAssigned) from Tbl)  +1
WHERE sinceLastAssigned = 0

Open in new window

0
 
LVL 1

Author Closing Comment

by:penlandt
ID: 40561404
Thank you very much, this was exactly what I needed!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

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 Copy Database Wizard 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.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

756 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