我有两个表，hires_owner和项目，其中hirers_owner是项目中数据的摘要表。我想用项目数据定期更新hires_owner。表结构如下：
**hires_owner**
id INT(11) AUTO-INCREMENT
owner CHAR(25) UNIQUE
hires_total INT(3)
Sample data:
1, tim, 0
2, jack, 3
3, brian, 1
etc.
和
**projects**
id INT(11) AUTO-INCREMENT
date DATE() **this is the report date stamp, not date of activity
owner CHAR(25)
accept DATE()
sample data:
1, 2014-02-01, jack, 2014-01-02
2, 2014-02-01, jack, 2014-01-03
3, 2014-02-01, tim, NULL
etc.
该查询为我提供了要推送到hires_owner表中的结果：
select owner, count(accept)
from projects
where date = (select max(date) from projects)
group by owner
...但是我似乎无法正确获取更新查询。这是一种尝试：
update hires_owner h
set hires_total = p.Hires
(select owner, count(accept) as Hires
from projects
where date = (select max(date) from projects)
group by owner) p
where p.owner = h.owner
最佳答案
尝试这个：-
update
hires_owner h
inner join
(select owner, count(accept) num_c
from projects
where date = (select max(date) from projects)
group by owner) p
on h.owner = p.owner
set h.hires_total = num_c
关于mysql - 带有多个表和左外部联接的mysql update select语句，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22182646/