Solved

qry not updateable error

Posted on 2011-03-19
7
228 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now