I am trying to set the values of a field (dbo.temp_dentist.clinic_id - not a pk) to the same as the PK in the dbo.Clinics table, where the clinic_ref and clinicref fields from the two table match.
The query below runs, however it updates all the dbo.temp_dentist.clinic_id values to the first clinic_id from the dbo.clinics table. Am thinking it's something to do with one of the WHERE clauses or the JOIN but I am unsure. Any help would be massively appreicated.
update dbo.temp_dentist set dbo.temp_dentist.clinic_id = dbo.Clinics.clinic_id from dbo.Clinics where dbo.temp_dentist.clinic_ref in ( select dbo.Clinics.clinicref from dbo.Clinics INNER JOIN dbo.temp_dentist on dbo.temp_dentist.clinic_ref = dbo.Clinics.clinicref where dbo.temp_dentist.clinic_ref = dbo.Clinics.clinicref)