Search code examples
mysqlsqlsql-updatemysql-error-1054

Column not found when trying a cross database update in mysql

I'm trying to copy the contents of a column in one mysql database to an identical table in another mysql database.

I'm using:

UPDATE db1.table
SET    db1.table.name = db2.table.name, 
       db1.table.address = db2.table.address
WHERE  db1.table.id = db2.table.id;

I'm getting the error 1054: Unknown column 'db2.table.id' in 'where clause'.

Both tables have an id column, so I'm not sure why it won't work. I'm logged in as admin, and have full rights to both databases.

Solution 
  • UPDATE db1.table
JOIN   db2.table
ON     db1.table.id = db2.table.id
SET    db1.table.name = db2.table.name, 
       db1.table.address = db2.table.address