mysql update select 从查询结果中更新数据

在遇到需要update设置的参数来自从其他表select出的结果时,需要把update和select结合使用,不同数据库支持的形式不一样,在mysql中如下:

update A inner join(select id,name from B) c on A.id = c.id set A.name = c.name;

根据AB两个表的id相同为条件,把A表的name修改为B的sql语句就如上所。

 

复杂的：

UPDATE user_online_month_atu a
INNER JOIN (
SELECT
user_id,
sum(c.online_times) as online_times,
SUM(c.login_count) as login_count,
Sum(c.view_page_count) as view_page_count,
LEFT(c.log_date,length(c.log_date) - 2) as date
FROM
user_online_time_atu c
GROUP BY
c.user_id ,date
) b ON a.user_id = b.user_id AND a.`month`=b.date
SET a.online_time = b.online_times ,a.login_count=b.login_count,a.view_page_count=b.view_page_count

 

UPDATE user_online_month a
INNER JOIN (
SELECT
user_id,
sum(c.online_times) as online_times,
SUM(c.login_count) as login_count,
Sum(c.view_page_count) as view_page_count,
LEFT(c.log_date,length(c.log_date) - 2) as date
FROM
user_online_time c
GROUP BY
c.user_id ,date
) b ON a.user_id = b.user_id AND a.`month`=b.date
SET a.online_time = b.online_times ,a.login_count=b.login_count,a.view_page_count=b.view_page_count;