带有子查询值的复杂MySQL更新
带有子查询值的复杂MySQL更新
提问于 2013-12-29 17:33:46
在开始的时候，圣诞快乐！
我跟踪了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);回答 2
Stack Overflow用户
回答已采纳
发布于 2013-12-29 17:44:27
我会使用一个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;Stack Overflow用户
发布于 2013-12-29 17:40:56
UPDATE definitions_words d
JOIN (SELECT word_id, current_definition_id, COUNT(*) as position
FROM memo_words
WHERE current_definition_id IS NOT NULL
GROUP BY current_definition_id, word_id) m
ON d.word_id = m.word_id AND d.definition_id = m.current_definition_id
SET d.position = m.positionSQLFIDDLE
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/20828084
复制相关文章
相似问题
腾讯云开发者
