updating one table using a query of another table

alnoir

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.

Mar 6 '08 #1

ronverdonk

What a curious select. Since you do not specify in your select from which database the col1, val1, col5, val5 values are to be compared, I assume you mean db1.col1=db2.col1 and db1.col5=db2.col5. If not forget the next statement Ronald

Mar 6 '08 #2

alnoir

What a curious select. Since you do not specify in your select from which database the col1, val1, col5, val5 values are to be compared, I assume you mean db1.col1=db2.col1 and db1.col5=db2.col5. If not forget the next statement

Expand|Select|Wrap|Line Numbers

1. UPDATE db1 SET col2=(SELECT col2 FROM db2 WHERE db1.col1=db2.col1 AND db1.col5=db2.col5)

Ronald

Thank you for your response. I'm not actually trying to directly compare the values from the two databases. This sql query is more of a test. After which, I will try and do some automation. I'm simply viewing the first database and picking one record out (visually, not programmatically). Then, since I know that the row I'm looking at is also a record in the second database, the condition is hard coded with those values, so that I can pull a value from that record. This value that I pull from a row in the second database is one of the values missing from the same row in the first database. With this value I'm trying to update the record in the first database.

I hope this makes things clearer. Thank you for your help.

Mar 6 '08 #3