mysql - 使用子查询中的值进行复杂的 MySQL 更新

Question

我有以下 MySQL 表:

memo_words
+----+-----------------------+---------+
| id | current_definition_id | word_id |
+----+-----------------------+---------+
|  1 |                     1 |       1 |
|  2 |                     2 |       1 |
|  3 |                     1 |       2 |
|  4 |                     2 |       2 |
+----+-----------------------+---------+

words
+----+
| id |
+----+
|  1 |
|  2 |
+----+

definitions
+----+
| id |
+----+
|  1 |
|  2 |
+----+

definitions_words
+---------+---------------+----------+
| word_id | definition_id | position | 
+---------+---------------+----------+
|       1 |             1 |        0 |
|       1 |             2 |        0 |
|       2 |             1 |        0 |
|       2 |             2 |        0 |
+---------+---------------+----------+

我希望从下面的查询返回的任何行都更新相关的(definition_id, word_id) definitions_words.position 以及值别名position:

SELECT word_id, current_definition_id as definition_id, COUNT(*) as position
FROM memo_words
WHERE current_definition_id IS NOT NULL
GROUP BY current_definition_id, word_id;

+---------+---------------+----------+
| word_id | definition_id | position |
+---------+---------------+----------+
|       1 |             1 |        1 |
|       2 |             1 |        1 |
|       1 |             2 |        1 |
|       2 |             2 |        1 |
+---------+---------------+----------+

在一个查询中。

因此此操作的结果应如下所示:

definitions_words
+---------+---------------+----------+
| word_id | definition_id | position | 
+---------+---------------+----------+
|       1 |             1 |        1 |
|       1 |             2 |        1 |
|       2 |             1 |        1 |
|       2 |             2 |        1 |
+---------+---------------+----------+

<小时/>

DDL:

CREATE TABLE memo_words( id int, current_definition_id int, word_id int, PRIMARY KEY(id));
INSERT INTO memo_words VALUES (1, 1, 1), (2, 2, 1), (3, 1, 2), (4, 2, 2);

CREATE TABLE words(id int, PRIMARY KEY(id));
INSERT INTO words VALUES(1), (2);


CREATE TABLE definitions(id int, PRIMARY KEY(id));
INSERT INTO definitions VALUES(1), (2);

CREATE TABLE definitions_words(word_id int, definition_id int , position int);
INSERT INTO definitions_words VALUES(1,1,0), (1,2,0), (2,1,0), (2,2,0);

Answer 1

我会使用join和子查询来做到这一点:

update definitions_words dw join
       (SELECT word_id, current_definition_id as definition_id, COUNT(*) as position
        FROM memo_words
        WHERE current_definition_id IS NOT NULL
        GROUP BY current_definition_id, word_id
       ) mw
       on dw.word_id = mw.word_id and dw.definition_id = mw.definition_id
    set dw.position = mw.position;