4

I have a table

create table w (
  id integer primary key,
  --
  min_xyz double,
  max_xyz double,
  min_abc double,
  max_abc double
);

with the min* and max* values currently being null.

I have another table

create table n (
    id   integer primary key,
    ---
    xyz  float,
    abc  float
);

There is a n:n relation between these tables:

create table n_in_w (
  n_id   integer not null references n,
  w_id   integer not null references w
  --- further attributes
);

Now, I'd like to fill the min* and max* attributes in w with the respective values in n via the connection via n_in_w. The following would do

update w set 
  min_xyz = (select min(xyz) from n, n_in_w where n.id = n_in_w.n_id and n_in_w.w_id = w.id),
  max_xyz = (select max(xyz) from n, n_in_w where n.id = n_in_w.n_id and n_in_w.w_id = w.id),
  min_abc = (select min(abc) from n, n_in_w where n.id = n_in_w.n_id and n_in_w.w_id = w.id),
  max_abc = (select max(abc) from n, n_in_w where n.id = n_in_w.n_id and n_in_w.w_id = w.id);

but I fear that the performance of this sql query is not optimal as it has to scan n 4 times for each n.id;

Ideally, I am looking for something like

update (
  select
    w.id,
    /*----*/
    w.min_xyz,
    w.max_xyz,
    w.min_abc,
    w.max_abc,
    /*-----*/
    min(n.xyz) min_n_xyz,
    max(n.xyz) max_n_xyz,
    min(n.abc) min_n_abc,
    max(n.abc) max_n_abc
  from 
    w, n_in_w, n
  where
    w.id = n_in_w.w_id and
    n.id = n_in_w.n_id
) i
set
  i.min_xyz = i.min_n_xyz,
  i.max_xyz = i.max_n_xyz,
  i.min_abc = i.min_n_abc,
  i.max_abc = i.max_n_abc;

(or something similar), but it seems it's not possible with mysql.

1 Answer 1

8

MySQL syntax:

UPDATE 
      w 
  JOIN
      ( SELECT
              n_in_w.w_id,
              MIN(n.xyz) AS min_n_xyz,
              MAX(n.xyz) AS max_n_xyz,
              MIN(n.abc) AS min_n_abc,
              MAX(n.abc) AS max_n_abc
        FROM 
              n_in_w
          JOIN
              n
            ON 
              n.id = n_in_w.n_id
        GROUP BY
              n_in_w.w_id 
      ) nw
    ON  
      nw.w_id = w.id
SET
      w.min_xyz = nw.min_n_xyz,
      w.max_xyz = nw.max_n_xyz,
      w.min_abc = nw.min_n_abc,
      w.max_abc = nw.max_n_abc;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.