Using JOIN to over write columns-CodePudding

I have a table that has over 30 columns, it's patient medical record data so it has many fields. I have another table that Verified doctor information.

Say table 1 has columns patientID, patientName, patientAddress, ...., PrescriberID, PrescriberName, PrescriberAddress, PrescriberCity, PrescriberState, PrescriberZip, PrescriberPhone, ... plus many others

Table 2 has PrescriberID, PrescriberName, PrescriberAddress, PrescriberCity, PrescriberState, PrescriberZip, PrescriberPhone

How do I query so that if PrescriberID from table 1 is in table 2, name/address/city/state/zip/phone come from table 2, but if not, leave the information that was there?

EDIT: I Tried a suggestion and it worked. Thank you.

SELECT t1.Id, t1.PBM_ID, t1.CLIENT_ID, t1.CLAIMNUMBER, t1.PATIENTLNAME, t1.PATIENTFNAME, t1.PATIENTGENDER, t1.PATIENTADDRESS1, t1.PATIENTADDRESS2, t1.PATIENTCITY, t1.PATIENTSTATE, t1.PATIENTZIP, t1.PATIENTDATEOFBIRTH, t1.PATIENTCELLPHONE, t1.DATEOFINJURY, t1.CLAIMORGIN, t1.DOS, t1.SUBCARRIER, t1.GROUPDESCRIPTION, t1.SUBGROUP, t1.POLICYNUMBER, t1.JURISDICTIONSTATE, t1.ADJUDICATIONDATE, t1.CLIENTBILLRECEIVEDDATE, t1.PRESCRIBERDEA, t1.PRESCRIBERNPI, COALESCE(t2.Name, t1.PRESCRIBERNAME) as PRESCRIBERNAME, COALESCE(t2.Address, t1.PRESCRIBERADDRESS) as PRESCRIBERADDRESS, COALESCE(t2.City, t1.PRESCRIBERCITY) as PRESCRIBERCITY, COALESCE(t2.State, t1.PRESCRIBERSTATE) as PRESCRIBERSTATE, COALESCE(t2.Zip, t1.PRESCRIBERZIP) as PRESCRIBERZIP, COALESCE(t2.Phone, t1.PRESCRIBERPHONE) as PRESCRIBERPHONE, t1.PHARMACYPAIDDATE, t1.PHARMACYNABP, t1.PHARMACYNPI, t1.PHARMACYTAXID, t1.PHARMACYNAME, t1.PHARMACYPHONE, t1.PHARMACYADDRESS, t1.PHARMACYCITY, t1.PHARMACYSTATE, t1.PHARMACYZIP, t1.THIRDPARTYREPRICERNABP, t1.THIRDPARTYNAME, t1.THIRDPARTYNETWORK, t1.THIRDPARTYCLAIMSOURCE, t1.RXNUMBER, t1.DRUGNDC, t1.DRUGGPI, t1.DRUGNAME, t1.DRUGSTRENGTH, t1.DRUGTYPE, t1.ISCOMPOUND, t1.INDIVIDUALMED, t1.CUMULATIVEMED, t1.LEGALCLASS, t1.DAW, t1.QUANTITY, t1.DAYSSUPPLY, t1.REFILLNUMBER, t1.INVOICENUMBER, t1.BILLEDDATE, t1.BILLEDAMOUNT, t1.AWP, t1.STATEFEE_UCPRICE, t1.SAVINGS, t1.EMPLOYERNAME, t1.PANUMBER, t1.PAAPPROVINGADJUSTER, t1.ADJUSTERNAME, t1.ADJUSTEREMAIL, t1.ADJUSTERPHONE, t1.BRANCHCODE, t1.BRANCHNAME, t1.LoadDate, t1.FileType, t1.AdjusterFname, t1.AdjusterLname, t1.Clean_NDC, t1.CASEMANAGERNAME, t1.CASEMANAGEREMAIL
FROM PBM_Common as t1
LEFT OUTER JOIN NPI_Records as t2 
ON t1.PRESCRIBERNPI = t2.NPI_Number;

CodePudding user response：

First start by writing a SQL statement that will give you the result set that contains your logic. Use a LEFT OUTER JOIN to get all records from table1 and only those records from table2 that match for each table1.prescriberid. Use COALESCE() to pick the table2 values, unless they are null, then use table1 values:

SELECT t1.patientID, t1.patientName, t1.patientAddress, ...., t1.PrescriberID, COALESCE(t2.PrescriberName, t1.PrescriberName) as PrescriberName, COALESCE(t2.PrescriberAddress, t1.PrecsriberAddress) as PrescriberAddress,  ... plus many others
FROM table1 as t1
    LEFT OUTER JOIN table2 as t2 
        ON t1.PrescriberID = t2.PrescriberID;

You could just stop there as you have the data you want. If you must override the values in your table1 table, then you'll need to develop this SELECT statement into an UPDATE statement. You can see some common ways to do this here. For this example I will use the CTE approach:

With correct_date AS
(
    SELECT t1.patientID, t1.patientName, t1.patientAddress, ...., t1.PrescriberID, COALESCE(t2.PrescriberName, t1.PrescriberName) as PrescriberName, COALESCE(t2.PrescriberAddress, t1.PrecsriberAddress) as PrescriberAddress,  ... plus many others
    FROM table1 as t1
        LEFT OUTER JOIN table2 as t2 
            ON t1.PrescriberID = t2.PrescriberID
)
UPDATE T2
SET PrescriberName = cd.PrescriberName, 
    PrescriberAddress = cd.PrescriberAddress
    ... plus many others
FROM table2 as T2
    INNER JOIN correct_data cd 
        ON T2.PatientID = cd.PatiendID

Probably best to make a backup of table1 before running anything undo-able like an UPDATE statement.

Lastly, I think it's worth pointing out that this schema is terrible. Why store attributes of the prescriber in both tables where situations like the one you are trying to solve for can even exist. Just remove these columns from Table1 and leave only PrescriberID and then join in Table2 when you need those values.