I'm trying to update some records using the UPDATE and SELECT query.
I have two databases. The first database (db1) is a subset of the second database (db2). However, the first database is missing information in some of the columns that is found in the second database. So I want to query the second database for that information and update the records in the first database.
DB1:
________________________
| col1 | col2 | col3 | col4 | col5 |
=====================
| val1 | null | null | null | val5 |
------------------------------------
| val1 | null | null | null | val5 |
------------------------------------
| val1 | null | null | null | val5 |
------------------------------------
DB2:
_________________________
| col1 | col2 | col3 | col4 | col5 |
======================
| val1 | val2 | val3 | val4 | val5 |
----------------------------------------
| val1 | val2 | val3 | val4 | val5 |
----------------------------------------
| val1 | val2 | val3 | val4 | val5 |
----------------------------------------
I'm using this mysql statement (keep in mind this is an abstraction from what the actual statement is):
UPDATE db1 SET
col2 = (SELECT col2 FROM db2 WHERE (col1 = val1) AND (col5 = val5))
WHERE (col1 = val1) AND (col5 = val5);
However, I get this error message:
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 'SELECT col2 FROM db2 WHERE (col1 = val1) AND (col5 = val5))
(0 ms taken)
I'm not sure what in the syntax is wrong. After two hours of searching I still can't find the answer.
Does anyone have any idea?
Thanks.