467,872 Members | 1,605 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,872 developers. It's quick & easy.

updating one table using a query of another table

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
  • viewed: 1515
Share:
2 Replies
ronverdonk
Expert 4TB
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
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.

Similar topics

11 posts views Thread by Jason | last post: by
1 post views Thread by Chris Jackson | last post: by
2 posts views Thread by Ray Holtz | last post: by
3 posts views Thread by Jon Agiato | last post: by
33 posts views Thread by bill | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.