Update Statement returns error - Why? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Update Statement returns error - Why?

Author

Topic

lassies_mom2003
Starting Member

3 Posts

Posted - 2006-03-31 : 10:42:48

I'm not experienced in Access & SQL, but I know more than the others in my office. My customer asked me to update records in his database with information he had in Excel. I imported the spreadsheet into Access, matched the datatypes and began working on a query to update the records. Here are the details:

Update records in tblMain with the records in tblinput (the imported data from Excel). Serial numbers are supposed to be unique, however there are duplicate records in tblMain. There are no serials in tblinput that are not in tblMain.

I wrote the following query, but get an error "Operation must use an
updateable query). Can you explain 1) whats happening and 2) if the
query I wrote will do what I need?

UPDATE tblMain
SET fss = (SELECT fss
FROM tblinput
WHERE tblmain.serial = tblinput.serial);

Thank you

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-31 : 11:22:52

Access does not recieve joy from updating a table that is itself referenced in a nested subquery.

Your SQL statement is more properly written using this syntax:

UPDATE	tblMain
SET	fss = tblinput.fss
from	tblMain
	inner join tblinput on tblMain tblmain.serial = tblinput.serial;

I think this will compile for you.

lassies_mom2003
Starting Member

3 Posts

Posted - 2006-04-03 : 10:50:27

Thanks, I ran this and I'm getting a request to provide the serial number I want.

I need the tblinput to feed the serial into the tblMain- and I think thats where I'm confused.

I don't think the query above is doing that.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-04-03 : 14:24:26

You ran this in what and were requested by who?

tblInput is linked to tblMain through the join clause. I don't understand what the problem is.

lassies_mom2003
Starting Member

3 Posts

Posted - 2006-04-03 : 15:08:26

Thanks for getting back with me. Sorry I wasn't more clear.

I typed the query into MS Access SQL view and ran it, but I get a popup window from the application asking to define the parameter value.

There are 4000 records to update - I was trying to take the data from tblinput and slide it into the corresponding records in tblMain.

I hope that was a little more clear.

Thanks for your help!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-04-03 : 15:19:54

What parameter? There are no parameters in the sql code I gave you.

Are you sure you have the table and column names correct?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources