Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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