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.
2  1784 
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.co l1 and db1.col5=db2.co l5. If not forget the next statement - UPDATE db1 SET col2=(SELECT col2 FROM db2 WHERE db1.col1=db2.col1 AND db1.col5=db2.col5)
Ronald
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.co l1 and db1.col5=db2.co l5. If not forget the next statement - 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 programmaticall y). 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.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Jason |
last post by:
Let's say I have an html form with 20 or 30 fields in it. The form
submits the fields via POST to a php page which updates a table in a
database with the $_POST vars. Which makes more sense?
1) simply UPDATING the values for all fields in the table, whether or
not any particular field has actually changed
2) running a second SELECT statement and comparing the $_POST vars to
the returned values, and only UPDATING those that have...
|
by: Chris Jackson |
last post by:
I'm a novice Access user and am not sure how to solve the following problem.
Any help with the following would be greatly appreciated!
I have two tables with identical structures, the first holds the data input
for a questionnaire, the second holds the scores from the questionnaire, to
move the scores I've set up 2 action queries, the first appends the record
to the second table, the second action query 'updates' the results by...
|
by: Laura |
last post by:
Here's the situation:
I'm trying to use an update query to copy data from one row to another.
Here is the situation:
I have 5 companies that are linked to each other. I need to show all 5
companies on a form. 3 of the companies have common employees. I have a
table that looks like this:
|
by: Ray Holtz |
last post by:
I have a form that shows a single record based on a query criteria.
When I click a button it is set to use an append query to copy that
record to a separate table, then deletes the record from the first
table. Both tables have a 'DateTime' field that shows when it was last
updated using the Now function.
What I am trying to do is get the 'DateTime' field updated after the
append query copies it to the new table. I have tried updating...
|
by: Jon Agiato |
last post by:
Hi, I am trying to use a data grid in a web application in which I have
three tiers. The DataGrid is not set up to a data source, or a data adapter,
so everytime I make a change I send the cell information to another object
which forms an SQL query and makes the query to the DB (actually to a
DBFacade object). In any case, please see the code I have listed below for
my update DataGrid event. With this code, the DB is updated using the old...
| | |
by: beil.jp |
last post by:
Hi All -
I'm new to Google Groups and just a novice user of Access, so please
forgive me if my question is elementary:
I am having a problem using the "update query" for my first time. In
my database, I have two tables. Both of the tables have duplicate
data, though the "Initial Data" table has many more records than the
"Filter Two" table due to a process of elimination that has occurred in
the past week. Both tables have 5 colums...
|
by: John T Ingato |
last post by:
I have a contacts table with name address and such but are missing all phone
numbers in the phone number fields. I have just received an updated
customer list in Excel and have imported into a new table.
Can I run a query that will take the phone number only from the new table
and update the old tables phone number fields from empty to the proper
information?
|
by: somanyusernamesaretakenal |
last post by:
What I am trying to achieve:
Basically I have generated a report in access. This report needs to be updated using excel. (Updating the new data, not changing existing data)
What I did was I imported the existing report into Excel (Tools – Office Links – Analyze It with MS Excel). Then I linked the report to the Excel file. (File – Get External Data – Link tables) and a link table was automatically generated.
I wanted to test the link,...
|
by: bill |
last post by:
In an application I am writing the user can define a series of
steps to be followed. I save them in a sql database using the
field "order" (a smallint) as the primary key.
(there are in the range of 20 steps)
On the admin page the steps are listed, in "order" order and the
user can create new steps and assign an order and all is well.
The problem may come in using a renumber function which should
take the steps in their current order...
|
by: hrreece |
last post by:
I have an Access 2002 database that has a form that can be used to review individual records. At the bottom of the form are buttons that are linked to functions that allow the user to "Find a record using search criteria", "Delete the current record" and so on. After a user has used the search criteria to find a specific record, I would like to use the "delete" function on the form to not only delete the record, but also update another table. ...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
