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.