SQLTeam.com | Weblogs | Forums

Having trouble with updating

I have three tables.

Table 1

| ClaimID  | Patient  | Proc_Code| Proc_Cost|
| 1        | Tom      | DTR      | 20       |
| 1        | Tom      | OPR      | 30       |
| 1        | Tom      | TRR      | 20       |
| 2        | Bruce    | YUR      | 70       |

Table 2

| ClaimID  |PatientDOB| S_Name   | Date     |
| 1        | 12-2-1989| ABC      | 11-2-2022|
| 2        | 10-3-1987| ABC      | 11-2-2022|
| 3        | 12-2-1989| ABC      | 11-2-2022|
| 4        | 12-2-1989| ABC      | 11-2-2022|

Table 3

| ClaimID  |PatientDOB| PName    | Date     |Proc_Code | Proc_Cost| 
| 1        | 12-2-1989| Tom      | 11-2-2022|Null      | Null     |
| 2        | 10-3-1987| Hary     | 11-2-2022|Null      | Null     |
| 3        | 12-2-1989| Mary     | 11-2-2022|Null      | Null     |
| 4        | 12-2-1989| Steve    | 11-2-2022|Null      | Null     |

Table 1 & 2 are updated automatically when we ingest EDI file from EDI converter. Table 3, I update with left join from Table 2 and insert into statement. Table 1 has different Proc code and Proc cost for each claimID. I want to update these to table 3 which would add a new row and update the Proc Code and Proc Cost. It should look something like this:

Table 3

| ClaimID  |PatientDOB| PName    | Date     |Proc_Code | Proc_Cost| 
| 1        | 12-2-1989| Tom      | 11-2-2022| DTR      | 20       |
| 1        | 12-2-1989| Tom      | 11-2-2022| OPR      | 30       |
| 1        | 12-2-1989| Tom      | 11-2-2022| TRR      | 20       |
| 2        | 10-3-1987| Hary     | 11-2-2022| YUR      | 70       |
| 3        | 12-2-1989| Mary     | 11-2-2022|#value    | #value   |
| 4        | 12-2-1989| Steve    | 11-2-2022|#value    | #value   |

#value = some value that will be in table 1.

ClaimID #2 will also have multiple Proc code and Proc cost. So will other Claim IDs. I am unsure as to how do I do this? I tried joining Table 3 with Table 1, the problem that arise was that all the information from Table 1 was pasted on the bottom rather that how I want it to be.

I tried update as well:

UPDATE table3
SET table3.Proc_Code = table1.Proc_Code
FROM table3
JOIN table1 ON table3.ClaimID = table1.ClaimID
WHERE  table3.ClaimID = table1.ClaimID;

This works but it only updates the top one claimeID and ignores other. I am thinking there should be a loop? that will update and add new row for the new value? I am not sure how to do it?