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

VB-Forms: SQL Update Query

P: 2
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
Sep 24 '08 #1
Share this Question
Share on Google+
2 Replies


Plater
Expert 5K+
P: 7,872
You would need to split that up a bit.
In a regular database system, you would create a StoredProcedure to do that. I am fairly certain that Access can do it too, but I think it has different terminology.


You could try making it be:
UPDATE TableA
SET myfieldA = (SELECT 1 myfieldB
FROM TableA INNER JOIN TableB
ON TableA.ID = TableB.ID)

That might not be right, but I once saw an article about sticking an int value in the SELECT statement somewhere would only return up to that amount of results?
Sep 24 '08 #2

P: 2
That didn't quite work out either - I think the problem is that Access doesn't speak the language perfectly. I solved the problem by building the update query I wanted in Access, and then right-clicking on the query design window and selecting "SQL View" - a feature I didn't know existed...

Anyway, it gave me the following code:
UPDATE tblA INNER JOIN
tblB ON tblA.ID = tblB.ID
SET tblA.myfield = [tblB].[myfield];

It looks to me as if this actually updates both tables in the join, so maybe tblB.myfield is getting copied into itself. When I put it into the query builder in Dataset Designer, I got the familiar 'unable to parse query text' message - but it works just fine despite that.
Sep 29 '08 #3

Post your reply

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