Updating MySQL innodb table based on multiple joins

Question

I have a table with the following (slightly simplified) structure

CREATE TABLE `oak_relation` (
 `o_id` int(10) unsigned NOT NULL,
 `k_id` bigint(20) unsigned NOT NULL,
 `initial` float unsigned NOT NULL,
 `xy` float unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`o_id`,`k_id`),
 KEY `xy` (`xy`)
) ENGINE=InnoDB

There are multiple instances of the same o_id, and multiple instances of the same k_id, but the primary key ensures there are only single instances of a row with an individual o_id & k_id. Values for initial range from 0 to 1.

I would like to run an update to generate values for "xy". The update should perform the following operation: For every row in the table, calulate the xy value;

xy = x * y 

where

x = `initial` value / number of rows matching the o_id of this row
y = total number of distinct o_ids / number of rows matching the k_id of the row

My query so far:

UPDATE
 oak_relation AS final
 INNER JOIN 
 ( 
 SELECT
 oak.o_id ,
 oak.k_id ,
 oak.initial,
 link.numLinks,
 COALESCE( ( oak.initial / link.numLinks ), 0 ) AS x
 FROM
 oak_relation AS oak
 LEFT OUTER JOIN 
 (
 SELECT
 o_id ,
 COALESCE( COUNT( k_id ), 0 ) AS numLinks
 FROM
 oak_relation
 GROUP BY
 o_id
 ) AS link ON oak.o_id = link.o_id
 ) AS xjoin
 INNER JOIN 
 ( 
 SELECT
 k_id ,
 COUNT( o_id ) AS numOforK ,
 COALESCE( ( tmp.totalO / COUNT( DISTINCT( o_id ) ) ), 0 ) AS y
 FROM
 oak_relation,
 (
 SELECT
 COUNT( DISTINCT( o_id ) ) AS totalO
 FROM
 oak_relation
 ) AS tmp
 GROUP BY
 k_id
 HAVING
 COUNT( o_id ) > 1
 ) AS yjoin
SET
 final.xy = xjoin.x * yjoin.y
WHERE
 final.o_id = xjoin.o_id 
 AND 
 final.k_id = xjoin.k_id
 AND
 xjoin.k_id = yjoin.k_id

The trouble I am having is my query doesn't seem to update all the rows and my SQL knowledge is a little limited in updates using joins. I thought perhaps it was just not updating if the xy result was 0 (if initial is 0), but selecting the expected result set shows that some xy = 0 values are being set, also tested by setting default xy value to some arbitrarily large number and examining the result after update. Some rows were definitely being set to 0.

So I'm a little stuck! For the rows where the calculation happens, it appears to work correctly, I just seem to not be selecting the correct set of data to update so it calculates for every row of the table.

Thanks very much in advance for any help or insight you might be able to provide.

Answer 1

I think this will do:

UPDATE 
 ( SELECT COUNT(DISTINCT o_id) AS total_oids 
 FROM oak_relation 
 ) AS d
 CROSS JOIN
 oak_relation AS u
 JOIN
 ( SELECT o_id, COUNT(*) AS matching_o_ids 
 FROM oak_relation
 GROUP BY o_id 
 ) AS o
 ON o.o_id = u.o_id
 JOIN
 ( SELECT k_id, COUNT(*) AS matching_k_ids 
 FROM oak_relation
 GROUP BY k_id 
 ) AS k
 ON k.k_id = u.k_id
SET
 u.xy = u.initial * d.total_oids / o.matching_o_ids / k.matching_k_ids ;

---

It would be good to check it out first by running the equivalent SELECT query:

SELECT
 u.o_id, u.k_id, u.initial,
 d.total_oids,
 o.matching_o_ids,
 k.matching_k_ids,
 u.initial * d.total_oids / o.matching_o_ids / k.matching_k_ids AS new_xy
FROM 
 ( SELECT COUNT(DISTINCT o_id) AS total_oids 
 FROM oak_relation 
 ) AS d
 CROSS JOIN
 oak_relation AS u
 JOIN
 ( SELECT o_id, COUNT(*) AS matching_o_ids 
 FROM oak_relation
 GROUP BY o_id 
 ) AS o
 ON o.o_id = u.o_id
 JOIN
 ( SELECT k_id, COUNT(*) AS matching_k_ids 
 FROM oak_relation
 GROUP BY k_id 
 ) AS k
 ON k.k_id = u.k_id ;

• osbert: That's fantastic, thank you! I had a feeling it should be so much simpler than what I had, and you've proved it to me :)