MS Access SQL update query based on another query

Question

My DB has existing data. I'm trying to implement some sort of security rights system. It doesn't need to be truly secure...just limit what each level can effectively change. Technically...one exists...but it needs to be beefed up.

I need to adjust the existing Rights level for people that are Instructors. I have a query (qInstructors) that lists a DISTINCT query of anyone in the class table listed as an Instructor. There are a total of 38 Instructors.

Now I need to update the User table to adjust the rights of those 38 people...and this is where I'm stuck. A simple update query, no problem. But I must not be searching with the correct term because I can't find anything to help me hammer out the SQL.

UPDATE tblUserList 
INNER JOIN tblUserList ON tblUserList.NTID = qInstructors.Instructor 
SET tblUserList.Rights = 2
WHERE [NTID]=[Instructor];

When I try to run this, I get a syntax error in the JOIN. This is beyond my SQL knowledge...any leads?

Gordon Linoff · Accepted Answer · 2015-09-24 14:32:26Z

2

I would suggest doing this using IN:

UPDATE tblUserList 
    SET tblUserList.Rights = 2
    WHERE [NTID] IN (SELECT [Instructor] FROM qInstructors);

The use of JOIN in an UPDATE clause varies by database. The IN version is ANSI standard and should work both in MS Access and in whatever back-end database you might be using.

answered Sep 24, 2015 at 14:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vamsi Prabhala · Accepted Answer · 2015-09-24 14:25:57Z

1

You were specifying the tblUserlist instead of qinstructors in the join clause.

UPDATE tblUserList 
INNER JOIN qInstructors ON tblUserList.NTID = qInstructors.Instructor 
SET tblUserList.Rights = 2

answered Sep 24, 2015 at 14:25

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

1 Comment

Rdster Over a year ago

This did resolve the error, and I can view the resulting rows that match....however I'm apparently using an un-updatedable query and cannot actually update.

JosephStyons · Accepted Answer · 2015-09-24 14:26:05Z

0

Create a query in Design View. Change the type to "Update Query".

Add your target table to the query, and add your existing "Give me instructors" query as well.

Drag a line from the ID in your target table to the corresponding ID returned by your Query.

Drag the field you want to update down to the grid at the bottom. In the "Update To" field, enter "Allowed" or "True" or whatever indicates that something is allowed for instructors.

answered Sep 24, 2015 at 14:26

JosephStyons

59.1k64 gold badges169 silver badges240 bronze badges

Collectives™ on Stack Overflow

MS Access SQL update query based on another query

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related