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

MSQL UPDATE table

benchpolo
100+
P: 142
Tablename: CLAIMS
Fields: PATID, REVENUE, CLAIMNO

DATA

235101133A, 6117.84, 200705455201111
235101133A, 6117.84, 200605599902888

Question?
I have a set of data records where the REVENUE is calculated monthly by PATID. The PATID can appear more than once in a set of data records where the CLAIMNO is unique.

The problem I'm having is that "unique" PATID can only have one calculated REVENUE. As you can see from above example 6117.84 appeared twice in the record set due to a different claimno.

What I want to accomplish is to keep the earliest or recent CLAIMNO entered in the system and update the REVENUE field, but zero out the remainder of the data whether there are more claimno related to the same PATID. Does it make sense?

Please advise. Thanks
May 2 '08 #1
Share this Question
Share on Google+
3 Replies


ck9663
Expert 2.5K+
P: 2,878
Tablename: CLAIMS
Fields: PATID, REVENUE, CLAIMNO

DATA

235101133A, 6117.84, 200705455201111
235101133A, 6117.84, 200605599902888

Question?
I have a set of data records where the REVENUE is calculated monthly by PATID. The PATID can appear more than once in a set of data records where the CLAIMNO is unique.

The problem I'm having is that "unique" PATID can only have one calculated REVENUE. As you can see from above example 6117.84 appeared twice in the record set due to a different claimno.

What I want to accomplish is to keep the earliest or recent CLAIMNO entered in the system and update the REVENUE field, but zero out the remainder of the data whether there are more claimno related to the same PATID. Does it make sense?

Please advise. Thanks
If the REVENUE is the same for all PATID, this should be enough

INSERT INTO YourTable (PATID, REVENUE)
select PATID, REVENUE from CLAIMS group by PATID

-- CK
May 2 '08 #2

benchpolo
100+
P: 142
So after I perform the insert statement will my result be like this.

235101133A, 6117.84, 200705455201111
235101133A, <null> , 200605599902888

Thanks.
May 2 '08 #3

ck9663
Expert 2.5K+
P: 2,878
Use ISNULL or replace all NULL with 0

-- CK
May 2 '08 #4

Post your reply

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