Solved

qry not updateable error

Posted on 2011-03-19
7
231 Views
Last Modified: 2012-05-11
I have created a query using a find duplicates query based on [mvris code]. I want to update the variantdiff column to true. access wont let me and I am not sure how to achieve this
UPDATE TblSMMTDataDifferences RIGHT JOIN [Find duplicates for TblSMMTDataDifferences] ON TblSMMTDataDifferences.[MVRIS CODE] = [Find duplicates for TblSMMTDataDifferences].[MVRIS CODE Field] SET TblSMMTDataDifferences.variantDiff = True
WHERE ((([Find duplicates for TblSMMTDataDifferences].[MVRIS CODE Field]) Is Not Null));

Open in new window

0
Comment
Question by:PeterBaileyUk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35170554
you can't JOIN at the UPDATE command
it must join at FROM clause

anyway, I think your code can be like this

UPDATE TblSMMTDataDifferences
SET variantDiff = True
WHERE NOT EXISTS (
	SELECT [MVRIS CODE Field]
	FROM [Find duplicates for TblSMMTDataDifferences] 
	WHERE TblSMMTDataDifferences.[MVRIS CODE] = [Find duplicates for TblSMMTDataDifferences].[MVRIS CODE Field]
)

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 35170755
its asking for a parameter TblSMMTDataDifferences.MVRIS CODE

something not quite right
0
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 500 total points
ID: 35170868
Sorry, the one I given in for SQL
try this one, it should working on Access

UPDATE TblSMMTDataDifferences
SET variantDiff = True
WHERE [MVRIS CODE] NOT IN (
	SELECT [MVRIS CODE Field]
	FROM [Find duplicates for TblSMMTDataDifferences] 
)

Open in new window

0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

by:PeterBaileyUk
ID: 35171099
its working in a sense but the results are not correct its trying to update all the rows in the table
there are twelve [mvris code field] in the duplicate query which means there are 24 rows in the main query as the dups have only two rows each.
0
 

Author Comment

by:PeterBaileyUk
ID: 35171105
done it the sql should read in and not not in
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35171108
Sorry (again).
I mistaken your given query "IS NOT NULL" with "IS NULL"
that why my query become NOT IN, instead of IN.

Anyway, you already figure it out :)
0
 

Author Comment

by:PeterBaileyUk
ID: 35171109
amended version is:
UPDATE TblSMMTDataDifferences SET TblSMMTDataDifferences.variantDiff = True
WHERE (((TblSMMTDataDifferences.[MVRIS CODE]) In (SELECT [MVRIS CODE Field]
	FROM [Find duplicates for TblSMMTDataDifferences] 
)));

Open in new window

0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question