question

Rogit avatar image
Rogit asked

SET values to same as ID from another table, WHERE values match in another column - Is returning same value for all records, please help

Hi

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)                    
updatejoins
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

malpashaa avatar image
malpashaa answered

Use this:

            
UPDATE TD            
   SET TD.clinic_id = TD.new_clinic_id            
  FROM (SELECT TD.clinic_id, C.clinic_id AS new_clinic_id            
          FROM dbo.Clinics AS C            
               INNER JOIN            
               dbo.temp_dentist AS TD            
               ON TD.clinic_ref = C.clinicref) AS TD            
 WHERE TD.clinic_id != TD.new_clinic_id;            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Piotr Rodak avatar image
Piotr Rodak answered

Check this out

update dnt            
set clinic_id = cli.clinic_id            
from dbo.temp_dentist dnt inner join dbo.Clinics cli             
    on dnt.clinic_ref = cli.clinicref            

It should work.

And here's a simple example:

 create table tt1 (a int, b int, c int)            
    go            
    create table tt2 (a int, b int, c int)            
    go            
    insert tt1(a, b, c)            
    select 1, 2, 3 union all            
    select 11, 22, 33 union all            
    select 111, 222, 333            
            
    insert tt2 (a, b, c)            
    select 1, 222, 3 union all            
    select 2, 2, 33 union all            
    select 3, 22, 333            
            
--original values            
    select * from tt1            
            
--update column a with values from tt2 where b in both tables is equal            
    update x            
    set a = y.a            
    from tt1 x inner join tt2 y on x.b = y.b            
            
--updated values            
    select * from tt1            
    go            
    drop table tt1            
    go            
    drop table tt2            
    go            

Regards

Piotr

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.