mysql - 如何根据mySql中的其他表更新字段值？

Question

我有包含相关字段的表 report_business_likes:

id, facebook_id, created_at,  some_info
--  -----------  -----------  ----------
1    123456789   '2013-12-23'   blabla

我还有另一个名为 businesses 的表，其结构如下:

id, fb_id,    data
--  -----     ----
33  123456789  xxx

我想用 businesses 表中的 id 替换 report_business_likes 字段 facebook_id。

在我的例子中，结果应该是:

id, facebook_id, created_at,  some_info
--  -----------  -----------  ----------
1    33          '2013-12-23'   blabla

如您所见，我将 123456789 替换为 33。

我怎样才能做到这一点？

我试过:

UPDATE `report_business_likes` SET facebook_id = BZ.id from 
  (select id from `businesses` where fb_id = 123456789 ) as BZ, 
   where  facebook_id = 123456789 AND date(created_at) = '2013-12-23';

但是得到语法错误:

[SQL] /*SELECT * FROM `report_business_likes` where facebook_id = 123456789 AND date(created_at) = '2013-12-23';*/

UPDATE `report_business_likes` SET facebook_id = BZ from 
(select id from `businesses` where fb_id = 123456789) as BZ, 
where  facebook_id = 123456789AND date(created_at) = '2013-12-23';
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from 
(select id from `businesses` where fb_id = 123456789) as BZ,' at line 3

请帮忙，

Answer 1

UPDATE report_business_likes 
    SET facebook_id =   (select id 
                        from businesses  
                        where facebook_id = 123456789 )
 WHERE facebook_id = 123456789 AND date(created_at) = '2013-12-23'

或

UPDATE RBL
SET RBL.facebook_id = B.id
FROM report_business_likes RBL INNER JOIN businesses B 
ON RBL.facebook_id = B.facebook_id