sql - Updating table with the closest value from a lookup matrix -

table 1

first table matrix containing reference values, this:

create table dm_matrix (x float, z float, avgvalue float)   insert dm_matrix values (1,1, rand()) insert dm_matrix values (1,2, rand()) ... insert dm_matrix values (4,3, rand()) insert dm_matrix values (4,4, rand()) 

creating matrix one

z\x |  1  |  2  |  3  |  4  | -----------------------------   1 |  .1 |  .7 |  .3 |  .2 |   2 |  .5 |  .1 |  .8 |  .6 |   3 |  .6 |  .2 |  .3 |  .9 |   4 |  .4 |  .3 |  .3 |  .5 | 

table 2

second table list of events coordinates:

create table dm_values  (vx float, vz float, v float)  insert dm_values (vx, vz) values (1 + rand()*3, 1 + rand()*3) ... insert dm_values (vx, vz) values (1 + rand()*3, 1 + rand()*3) 

so entries have coordinates, no values events

   vx  |  vz  |   v   | -----------------------   1.3  |  2.7 |  null |   2.6  |  2.7 |  null |   1.3  |  3.3 |  null |   1.9  |  1.1 |  null |   3.0  |  2.9 |  null |   ...  |  ... |   ... | 

task

i want update second table closest value first one. first entry (x = 1.3, z = 2.7) want value updated 1 lookup matrix x = 1 , z = 3 (.6). updated table this:

   vx  |  vz  |   v   | -----------------------   1.3  |  2.7 |   0.6 |   2.6  |  2.7 |   0.3 |   1.3  |  3.3 |   0.6 |   1.9  |  1.1 |   0.7 |   3.0  |  2.9 |   0.3 |   ...  |  ... |   ... | 

problem

i can -- in theory -- work cursors. table 1 has 2000 rows (~50x40) , table 2 rows in low millions. cursor approach take days, weeks or months complete. need better performing solution , grateful tips.

from can see, should able perform simple update using join, round values of vx , vz join condition, performance wise, you'd have test on dataset though.

here's basic method join data, note i've padded out insert scripts have complete matrix:

create table #dm_matrix     (       x float ,       z float ,       avgvalue decimal(2, 1)     )   insert  #dm_matrix values  ( 1, 1, rand() ) insert  #dm_matrix values  ( 1, 2, rand() ) insert  #dm_matrix values  ( 1, 3, rand() ) insert  #dm_matrix values  ( 1, 4, rand() ) insert  #dm_matrix values  ( 2, 1, rand() ) insert  #dm_matrix values  ( 2, 2, rand() ) insert  #dm_matrix values  ( 2, 3, rand() ) insert  #dm_matrix values  ( 2, 4, rand() ) insert  #dm_matrix values  ( 3, 1, rand() ) insert  #dm_matrix values  ( 3, 2, rand() ) insert  #dm_matrix values  ( 3, 3, rand() ) insert  #dm_matrix values  ( 3, 4, rand() ) insert  #dm_matrix values  ( 4, 1, rand() ) insert  #dm_matrix values  ( 4, 2, rand() ) insert  #dm_matrix values  ( 4, 3, rand() ) insert  #dm_matrix values  ( 4, 4, rand() )  select  *    #dm_matrix  create table #dm_values     (       vx decimal(2, 1) ,       vz decimal(2, 1) ,       v float     )  insert  #dm_values         ( vx, vz ) values  ( 1 + rand() * 3, 1 + rand() * 3 ) insert  #dm_values         ( vx, vz ) values  ( 1 + rand() * 3, 1 + rand() * 3 )  select  *    #dm_values  -- replace select update commands below update values select  v.vx ,         v.vz ,         m.avgvalue    #dm_values v         inner join #dm_matrix m on round(v.vx, 0) = m.x                                    , round(v.vz, 0) = m.z  drop table #dm_matrix drop table #dm_values 

and update this:

update v set v.v = m.avgvalue #dm_values v  inner join #dm_matrix m on round(v.vx, 0) = m.x , round(v.vz, 0) = m.z  select * #dm_values 

produces:

matrix:

x   z   avgvalue 1   1   0.6 1   2   0.9  -- row 2 below 1   3   0.4 1   4   0.5 2   1   0.7 2   2   0.4 2   3   0.5  -- row 1 below 2   4   0.5 3   1   0.4 3   2   0.1 3   3   0.3 3   4   0.8 4   1   0.1 4   2   1.0 4   3   0.5 4   4   0.5   

values:

vx  vz  v 1.8 2.8 null  -- x = 2, z = 3 1.3 1.5 null  -- x = 1, z = 2 

after update:

vx  vz  v 1.8 2.8 0.5 1.3 1.5 0.9 

note:

i've changed data type decimal(2, 1) purpose of post, may need modify based on actual dataset.