?
Solved

qry not updateable error

Posted on 2011-03-19
7
Medium Priority
?
233 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 2000 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

764 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