mysql - 如何使用 Group by 子句更新具有特定计数的多行？

Question

我想用数字 40 更新具有特定计数 (count(*)=2) 的多行。考虑以下示例:

create table test(id int not null primary key, day date not null, tst int not null  );
insert into test(id, day, tst) values(1, '2006-10-08', 0);

insert into test(id, day, tst) values(2, '2006-10-08', 0);

insert into test(id, day,tst) values(3, '2006-10-09', 0);

我使用了以下查询 UPDATE test SET tst = 40 WHERE (select day from test GROUP BY day having count(*)=2);

但它永远行不通。任何帮助请...

谢谢。

艾门

Answer 1

这是一个完整的例子(使用 Oracle 而不是 mysql，但语法即使不相同也应该相似):

CREATE TABLE TEST (tst int, DAY INT)

INSERT INTO TEST VALUES (2, 4);
INSERT INTO TEST VALUES (3, 4);
INSERT INTO TEST VALUES (4, 5);

UPDATE TEST
   SET tst = 40
 WHERE EXISTS (SELECT DAY FROM TEST t2 WHERE t2.day = test.day GROUP BY DAY HAVING COUNT(*) = 2)
SELECT * FROM TEST;


Result:
    TST DAY
1   40  4
2   40  4
3   4   5