样例有两个表:spec1、tmpdot
spec1 表 有一列:high
tmpdot 表 共有两个列:old、new
两个表的内容大概如下:
spec1 表:
high |
A |
B |
A |
C |
B |
D |
tmpdot 表:
old | new |
A | newA |
B | newB |
C | newC |
方法一
UPDATE a SET high = b.new
FROM spec1 a, tmpdot b
WHERE a.high=b.old
方法二
UPDATE a
SET high = b.new
FROM a LEFT JOIN b ON ( a.high = b.old )
一个复杂的栗子:一个更新多个字段的例子
UPDATE a JOIN b ON a.XX= b.XX
SET a.Y = b.Y, a.Z = b.Z
-- 正式版本 最终版 统计上班排名和时间
UPDATE mobile_user_total AS old
INNER JOIN
(
SELECT u_info.user_id, class.class_name, u_info.`name`, a.clock_date AS arrive_time, COUNT(b.user_id)+1 AS up_rank
FROM user_info u_info
JOIN user_class u_class ON u_info.user_id = u_class.user_id
JOIN class ON class.id = u_class.class_id
JOIN
# a 表作为考勤主表用来统计用户没有迟到的最早记录
(
SELECT u_clock.user_id, MIN(clock_date) AS clock_date, u_class.class_id
FROM user_clockrecords u_clock
JOIN user_class u_class ON u_class.user_id = u_clock.user_id
JOIN class ON u_class.class_id = class.id
WHERE DATE(clock_date) = cal_date AND DATE_FORMAT(u_clock.clock_date,'%H:%i:%s') < DATE_FORMAT(class.begin_time,'%H:%i:%s') #根据传入的日期
GROUP BY user_id
)AS a ON u_info.user_id = a.user_id
LEFT JOIN(
SELECT u_clock.user_id, MIN(clock_date) AS clock_date, u_class.class_id
FROM user_clockrecords u_clock
JOIN user_class u_class ON u_class.user_id = u_clock.user_id
JOIN class ON u_class.class_id = class.id
WHERE DATE(clock_date) = cal_date AND DATE_FORMAT(u_clock.clock_date,'%H:%i:%s') < DATE_FORMAT(class.begin_time,'%H:%i:%s') #根据传入的日期
GROUP BY user_id
)AS b ON a.class_id = b.class_id AND u_info.user_id <> b.user_id AND b.clock_date < a.clock_date
GROUP BY u_info.user_id
)AS new ON old.user_id = new.user_id AND old.`current_time` = cal_date #根据传入的日期
SET old.work_clock_time = new.arrive_time, old.up_ranking = new.up_rank;
方法三
UPDATE a
SET high = ( SELECT new FROM tmpdot WHERE old = a.high )
FROM spec1 a