0

For some reasons I am getting the following error when executing the below query: (What I am trying to achieve is to sync and merge two left-joined table to into one

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 'ENGINE=FEDERATED' at line 35

CREATE TABLE users AS (
  SELECT
    ID, user_email, user_registered,
    first_name.meta_value as first_name,
    last_name.meta_value as last_name,
    telephone.meta_value as telephone,
    country.meta_value as country,
    company.meta_value as company,
    address.meta_value as address,
    city.meta_value as city,
    professional_title.meta_value as professional_title,
    state.meta_value as state,
    areas_of_interest.meta_value as areas_of_interest
    FROM wp_users
    LEFT JOIN wp_usermeta AS first_name ON first_name.user_id=ID
        AND first_name.meta_key='first_name'
    LEFT JOIN wp_usermeta AS last_name ON last_name.user_id=ID
        AND last_name.meta_key='last_name'
    LEFT JOIN wp_usermeta AS telephone ON telephone.user_id=ID
        AND telephone.meta_key='telephone'
    LEFT JOIN wp_usermeta AS country ON country.user_id=ID
        AND country.meta_key='country'
    LEFT JOIN wp_usermeta AS company ON company.user_id=ID
        AND company.meta_key='company'
    LEFT JOIN wp_usermeta AS address ON address.user_id=ID
        AND address.meta_key='address'
    LEFT JOIN wp_usermeta AS city ON city.user_id=ID
        AND city.meta_key='city'
    LEFT JOIN wp_usermeta AS professional_title ON professional_title.user_id=ID
        AND professional_title.meta_key='professional_title'
    LEFT JOIN wp_usermeta AS state ON state.user_id=ID
        AND state.meta_key='state'
    LEFT JOIN wp_usermeta AS areas_of_interest ON areas_of_interest.user_id=ID
        AND areas_of_interest.meta_key='areas_of_interest'
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION 'mysql://*********';

Note: FEDERATED Engine is on and working on other non-joined table.

2
  • Still not clear why ENGINE = FEDERATED is used ? Nov 14, 2014 at 19:54
  • I wanted to remotely left-join and sync the two tables. but Now I am even interested to achieve the same without FEDERATED on local db if possible.
    – mediaroot
    Nov 14, 2014 at 20:23

1 Answer 1

1

To define a FEDERATED table the field definition on local table much match field types on remote table. You are creating a brand new table by joining existing tables. I don't think this is legal.

A local server with a database table, where the table definition matches that of the corresponding table on the remote server. The table definition is stored within the .frm file. However, there is no data file on the local server. Instead, the table definition includes a connection string that points to the remote table.

http://dev.mysql.com/doc/refman/5.5/en/federated-description.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.