MSQL UPDATE table

[benchpolo]

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

[ck9663]

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

[benchpolo]

So after I perform the insert statement will my result be like this.

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

Thanks.

[ck9663]

Use ISNULL or replace all NULL with 0

-- CK