求助关于联表UPDATE

Submitted by jdkjfi on 周四, 2008/06/26 - 12:37

UPDATE a
SET a.status = 1, a.up_date = b.up_date
FROM
(user_order_info_k a INNER JOIN misc_user_info b
ON a.feetermid = b.feetermid
AND a.serviceid = b.serviceid)
LEFT JOIN blacklist c
ON a.feetermid=c.phone
WHERE a.status = 0
AND b.status = 'A'
AND a.up_date < b.sync_date
AND c.id is null

这样一个UPDATE语句在MYSQL中应该如何实现
除了使用子查询和临时表

Taxonomy upgrade extras:

jdkjfi

周四, 2008/06/26 - 13:35

问题已解决，多谢叶总
UPDATE user_order_info_k a,misc_user_info b
SET a.status = 1,
a.up_date = b.up_date
WHERE a.feetermid = b.feetermid AND a.serviceid = b.serviceid AND
a.status = 0 AND
b.status = 'A' AND
a.up_date < b.sync_date AND
not exists(select 1 from blacklist c where a.feetermid = c.phone)

yejr

周四, 2008/06/26 - 14:23

见手册上的一个例子：

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

MySQL方案、培训、支持