CODEWITHSUNDEEP
mysql
laurent.lefevre
laurent.lefevre

Reputation: 60

MYSQL update with joins

When running the query :

UPDATE video v,akadam_post a,location l 
SET v.location_id=l.id 
WHERE a.encoding_id=v.id AND l.continentCode=a.loc_continent_code AND 
l.countryCode=a.loc_country_code AND l.regionCode=a.loc_region_code AND 
l.cityCode=a.loc_city_code

I get a syntax error, any idea ?

10:04:02    select id,encoding_id from akadam_post a left join location l on l.continentCode=a.loc_continent_code AND l.countryCode=a.loc_country_code AND l.regionCode=a.loc_region_code AND l.cityCode=a.loc_city_code  UPDATE video v,akadam_post a,location l SET v.location_id=l.id  WHERE a.encoding_id=v.id AND l.continentCode=a.loc_continent_code AND l.countryCode=a.loc_country_code AND l.regionCode=a.loc_region_code AND l.cityCode=a.loc_city_code  Error Code: 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 'UPDATE video v,akadam_post a,location l SET v.location_id=l.id  WHERE a.encoding' at line 3    0.00051 sec

Thanks !

Upvotes: 0

Views: 36

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

The syntax is not correct and it should as 

update video v
join akadam_post a on a.encoding_id=v.id
join location l on l.continentCode=a.loc_continent_code 
and l.countryCode=a.loc_country_code and l.regionCode=a.loc_region_code
and l.cityCode=a.loc_city_code
set v.location_id=l.id

Upvotes: 1

Related Questions