May 20, 2005 at 3:44 pm
Hello:
I am working on exporting a legacy billing system into Microsoft SQL Server 2000. There is an account table and a patient table. These tables are linked with a common column. The account table has only one entry, but can be connected to many patients in the patient table. Also, the account balance is stored in the account table.
I've been asked to add the account balance to one (and only one) of the patient accounts. When I do a JOIN, it adds this balance to all of the patients in the patient table. It doesn't matter which patient it is added to out of that account.
Does anybody know how to write an UPDATE statment that will grab the first patient it finds (in this example) and assign it the balance, and not update the balance on the remaining patients in that account?
Thanks in advance...
Brian
May 20, 2005 at 3:53 pm
Update P Set Balance = b.Balance
From Patients P join
(select min(patientID) as PatientID, AccountNo from Patients p2 group by AccountNno ) ap
on ap.patientID = p.patientID and p.AccountNo = ap.p.AccountNo
join
Balance b on ap.AccountNo = b.AccountNo
hth
* Noel
April 8, 2010 at 8:43 am
Thank you both for posting this problem and solution - I was able to apply it to a similar situation and it just saved me at least a day assuming that I could have ever figured it out.
April 8, 2010 at 4:43 pm
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply