Solved

qry not updateable error

Posted on 2011-03-19
7
229 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
  • 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

773 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