CODEWITHSUNDEEP
mysqlsql
snaggs
snaggs

Reputation: 5713

How to update field value based on other table in mySql?

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

Answers (4)

Barranka
Barranka

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

Vaibhav Parmar
Vaibhav Parmar

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

M.Ali
M.Ali

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

Mihai
Mihai

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

Related Questions