Reputation: 5713
I have table report_business_likes
with relevant fields:
id, facebook_id, created_at, some_info
-- ----------- ----------- ----------
1 123456789 '2013-12-23' blabla
I have other table named businesses
with followed structure:
id, fb_id, data
-- ----- ----
33 123456789 xxx
I want to replace in report_business_likes
field facebook_id
with id
from table businesses
.
In my case, the result should be:
id, facebook_id, created_at, some_info
-- ----------- ----------- ----------
1 33 '2013-12-23' blabla
As you can see I replaced 123456789
with 33
.
How can I achieve that?
I tried:
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';
But get syntax error:
[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
Please help,
Upvotes: 0
Views: 145
Reputation: 21067
You are trying to mix the syntax. The correct syntax for update
is:
update `Your table`
set `Field in yourTable` = `value or expression`
where `Conditions to filter the data in your table`
There's no place in the update
syntax for a from
clause.
Of course, if you want to update your field with the result of a subquery, you can (see M.Ali's answer).
Please take a look to the reference manual.
Upvotes: 1
Reputation: 643
you can try by the following:
Update set sb.facebook_id=b.id from
report_business _likes rb join business b on r.facebook_id=b.fb_id
Upvotes: 1
Reputation: 69594
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'
OR
UPDATE RBL
SET RBL.facebook_id = B.id
FROM report_business_likes RBL INNER JOIN businesses B
ON RBL.facebook_id = B.facebook_id
Upvotes: 4
Reputation: 26804
UPDATE report_business_likes r JOIN businesses b ON r.facebook_id=b.fb_id
SET r.facebook_id=b.id
Upvotes: 2