Search code examples
mysqlsqlsql-updatemysql-error-1054

MySQL: ERROR 1054 (42S22): Unknown column in 'where clause'

I'm doing some changes on a WordPress database. I need to replace the URLs in the GUID field on the wp-posts table with the URLs coming from another table called ebdurls. The description of the tables is as follows:

wp_posts: the field type for the two fields I need are:

ID -> bigint(20) unsigned

guid -> varchar(255)

And the table where I have the data I need to export to wp_posts is this:

ebdurls:

title -> varchar(255)

url -> varchar(255)

ebdid -> bigint(20) unsigned

Everything seems correct, but when I apply the next query it gives me an error that I really can't get. I've tried quoting fields, tables, etc... everywhere, but no luck.

mysql> update wp_posts set wp_posts.guid=ebdurls.url where wp_posts.id=ebdurls.ebdid;

ERROR 1054 (42S22): Unknown column 'ebdurls.ebdid' in 'where clause'

Where is the mistake?

Solution

  • You haven't specified what ebdurls is, add a from statement to your query:

    UPDATE 
    wp_posts, ebdurls
SET 
    wp_posts.guid = ebdurls.url 
WHERE 
    wp_posts.id=ebdurls.ebdid;

    edit: Bill was right, have fixed the format now. Oops.