SQL Server SQL Update Table Based On Data From Another Database

6kkfgxo0 于 2023-11-16 发布在其他

关注(0)|答案(1)|浏览(96)

I am attempting to update a table in one database with information from another table in a different database. On top of that I need a third table to establish the link between the two. Here's what I have:

Update
MT.dbo.[tblPatientEmployers]
Set
MT.dbo.[tblPatientEmployers].[PatientOccupation] = RTRIM(LTRIM(T.Field3)),
MT.dbo.[tblPatientEmployers].[PatientEmploymentStatusCode] = RTRIM(LTRIM(T.Field4)),
MT.dbo.[tblPatientEmployers].[PatientEmploymentFromDate] = RTRIM(LTRIM(T.Field5)),
MT.dbo.[tblPatientEmployers].[PatientEmploymentThruDate] = RTRIM(LTRIM(T.Field6)),
MT.dbo.[tblPatientEmployers].[PatientEmployerPhoneNumber] = RTRIM(LTRIM(T.Field7)),
MT.dbo.[tblPatientEmployers].[PatientNationalEmployerID] = RTRIM(LTRIM(T.Field8)),
MT.dbo.[tblPatientEmployers].[PatientEmployerName] = RTRIM(LTRIM(T.Field9)),
MT.dbo.[tblPatientEmployers].[PatientEmployerAddress1] = RTRIM(LTRIM(T.Field11)),
MT.dbo.[tblPatientEmployers].[PatientEmployerAddress2] = RTRIM(LTRIM(T.Field12)),
MT.dbo.[tblPatientEmployers].[PatientEmployerCity] = RTRIM(LTRIM(T.Field13)),
MT.dbo.[tblPatientEmployers].[PatientEmployerStateCode] = RTRIM(LTRIM(T.Field15)),
MT.dbo.[tblPatientEmployers].[PatientEmployerZipCode] = RTRIM(LTRIM(T.Field16))
from [MT_Work].[dbo].[impHL7Xref] as T
inner join [MT_Work].[dbo].[impHL7Xref] 
   ON MT.dbo.tblPatients.[MedicalRecordNumber] = [MT_Work].[dbo].[impHL7Xref].MRN
WHERE T.RecordType = 'PATEMP'

Which results in the error message: Msg 4104, Level 16, State 1, Line 20 The multi-part identifier "MT.dbo.tblPatients.MedicalRecordNumber" could not be bound.

sql-server

来源：https://stackoverflow.com/questions/26718367/sql-update-table-based-on-data-from-another-database

1条答案

按热度按时间

mnemlml81#

Your problem is that you have never told SQL Server how your tables are all related. You need each table in the FROM clause in order to make this work: next time you write an UPDATE statement, try first writing a SELECT statement that pulls all the values you want and displays them. This will help you ensure you have the correct connections.

Assuming I'm reading your conditions right, you're looking for the following:

UPDATE PE
SET
    PE.[PatientOccupation] = RTRIM(LTRIM(T.Field3)),
    PE.[PatientEmploymentStatusCode] = RTRIM(LTRIM(T.Field4)),
    PE.[PatientEmploymentFromDate] = RTRIM(LTRIM(T.Field5)),
    PE.[PatientEmploymentThruDate] = RTRIM(LTRIM(T.Field6)),
    PE.[PatientEmployerPhoneNumber] = RTRIM(LTRIM(T.Field7)),
    PE.[PatientNationalEmployerID] = RTRIM(LTRIM(T.Field8)),
    PE.[PatientEmployerName] = RTRIM(LTRIM(T.Field9)),
    PE.[PatientEmployerAddress1] = RTRIM(LTRIM(T.Field11)),
    PE.[PatientEmployerAddress2] = RTRIM(LTRIM(T.Field12)),
    PE.[PatientEmployerCity] = RTRIM(LTRIM(T.Field13)),
    PE.[PatientEmployerStateCode] = RTRIM(LTRIM(T.Field15)),
    PE.[PatientEmployerZipCode] = RTRIM(LTRIM(T.Field16))
FROM 
    [MT_Work].[dbo].[impHL7Xref] T
     INNER JOIN 
    MT.dbo.tblPatients P ON 
        T.MRN = P.MedicalRecordNumber
     INNER JOIN 
    MT.dbo.tblPatientEmployers PE ON 
        P.ID = PE.PatientID
WHERE T.RecordType = 'PATEMP'

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server SQL Update Table Based On Data From Another Database

1条答案

相关问题

热门标签

最新问答