Hi all,
I've been unable to find the answer I'm looking for on the forums, so I'm asking my first-ever question. I'm building a windows forms application in Visual Basic using Visual Studio 2008. I have an Access database behind it that I'm building on. I've only been playing around with VB for a few months now, so forgive any misconceptions I may have....here's what I'm trying to accomplish:
I have two tables in the database, and I need to update the value of a column in one of the tables based on whether or not there is a matching record in the other table. I'm trying to do this in the Dataset Designer by adding a new query to the datatable adapter, but having difficulty with it - for one thing, when I click on the Query Builder button, the wizard unable to parse the query text, which I take as an indication that it's not correct. Here's my best guess at the SQL statement (names changed for simplicity):
UPDATE TableA
SET myfieldA = (SELECT myfieldB
FROM TableA INNER JOIN TableB
ON TableA.ID = TableB.ID)
The sub select should always return a single result, as no duplicates are allowed for IDs in the table. With my table and field names plugged in, the wizard gives me an error in the SET clause and the 'unable to parse query text' line.
First, is this sort of update even possible? I just need the query to find matching records and copy over the values for those records, so I suspect there's a way. However, I've seen a lot of posts regarding the difficulty of using update queries like this and some that state that update queries don't support joins and/or sub selects. Second, if this isn't workable, what should I consider instead?
Thanks for any suggestions,
- John