By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,563 Members | 1,307 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,563 IT Pros & Developers. It's quick & easy.

updating one table using a query of another table

P: 23
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
Share this Question
Share on Google+
2 Replies


ronverdonk
Expert 2.5K+
P: 4,258
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
Mar 6 '08 #2

P: 23
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

Post your reply

Sign in to post your reply or Sign up for a free account.