MYSQL: Error Code 1054 on Update Statement

Question

I am trying to Update a column (say c1) from a table (say t1) sitting in database (say d1) with column say (c2) from table (say t2) sitting in database (say d2). The columns used to join two tables are column c3 in table t1 in database d1 and column c4 in table t2 in database d2.

My script for this looks like

UPDATE d1.t1
SET c1 = d2.t2.c2
WHERE d1.t1.c3 = d2.t2.c4;

On executing this, I get an Error 1054 stating that "Unknown column d2.t2.c4 in WHERE clause".

My code is as below:

UPDATE igr_raw_db.`master_database-v3_truncated`
SET `Transaction Type` = igr_keys.`transaction_type`.`Transaction_Type_Final`
WHERE
    igr_raw_db.`master_database-v3_truncated`.`document_type` = igr_keys.`transaction_type`.`Reg_Transaction_Type_Raw`;

Here, I have two databases - igr_raw_db and igr_keys. igr_raw_db has column Transaction Type which I am trying to set to value as per column Transaction_Type_Final in table transaction_type in database igr_keys and I want to match column document_type with column Reg_Transaction_Type_Raw of the two databases.

I have checked my columns in tables for which I am getting an error and the columns exists with exact same names.

Below is my igr_keys.transaction_type table

Sr No                           int         YES         
Reg_Transaction_Type_Raw    varchar(255)    YES         
Transaction_Type_Processed  varchar(255)    YES         
Transaction_Type_Final          varchar(255)    YES     

Below is a snipped of my igr_raw_db.master_database-v3_truncated table

consideration                  varchar(255) YES 
document_type                  varchar(255) YES     

Not able to understand why I am getting Error 1054.

Help will be much appreciated.

Thanks.

Answer 1

You must use multiple-table UPDATE Syntax:

UPDATE d1.t1
JOIN d2.t2 ON d1.t1.c3 = d2.t2.c4
SET d1.t1.c1 = d2.t2.c1;

If more than one row in source table matches a row in destination table then indefinite row from all matched ones will be used for updating. You must avoid this ambiguity (except the case when all these matched rows stores the same value, of course).

Of course you may use correlated query:

UPDATE d1.t1
SET c1 = ( SELECT c1
           FROM d2.t2
           WHERE d1.t1.c3 = d2.t2.c4 );

but this variant will produce an error if more than one row matches (can be fixed with LIMIT 1) or if correlated query uses another copy of the table to be updated (can be fixed by nested subquery). Also in most cases it should be slower.