加载中…mysql关联多表进行update更新操作
(2012-10-18 11:09:48)
标签：
杂谈mysql关联多表进行updmysql关联表更新 |
分类： 菜鸟DBA之MySQL |
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
mysql关联多表进行update更新操作
UPDATE Track
INNER JOIN MV
ON Track.trkid=MV.mvid
SET
Track.is_show=MV.is_show
WHERE
trkid<6
等同于
UPDATE Track,MV
SET
Track.is_show=MV.is_show
WHERE Track.trkid=MV.mvid and
trkid<6
【扩展】
根据结果集进行update更新操作
原表信息
表1：am_favorites_4
|
am_favorites_4表 |
|||
|
af_user_id |
af_tag_id |
af_content_id |
af_content_type |
|
374 |
0 |
535522 |
3 |
|
374 |
0 |
535522 |
3 |
|
374 |
89 |
535522 |
3 |
|
am_tag_user_4表 |
||
|
atu_user_id |
atu_tag_id |
atu_num |
|
374 |
0 |
9 |
|
374 |
89 |
9 |
更新结果目标表：am_tag_user_4
|
am_tag_user_4 表 |
||
|
atu_user_id |
atu_tag_id |
atu_num |
|
374 |
0 |
7 |
|
374 |
89 |
8 |
UPDATE am_tag_user_4 tag
INNER JOIN am_favorites_4 fav
ON tag.atu_tag_id=fav.af_tag_id and tag.atu_user_id=fav.af_user_id
INNER JOIN (SELECT
FROM am_favorites_4,am_tag_user_4
where atu_tag_id=af_tag_id and atu_user_id=af_user_id and af_content_id = 535522 and af_content_type=3 and af_user_id=374
group by af_user_id,af_tag_id) AS T1
ON tag.atu_tag_id=T1.af_tag_id and tag.atu_user_id=T1.af_user_id
SET tag.atu_num=tag.atu_num- T1.cnt
喜欢
0
赠金笔
前一篇：MySQL多表连接删除问题相关
后一篇：从习惯中惊醒