select for update

[garethl]

right i have been searching this forum looking for answers to my problem with no luck - i have been searching other forums and i now know how to do this using oracle database which isn't much help because i am using access!

what i want to do is a simple update setting the value of one field but i want to do it on only the records which are returned by a select statement

apparantly oracle has a construct

SELECT <select query here> FOR UPDATE <update query here>

can you do this in access? or is there a workaround?

specifically what i want to do is to read in an excel file which contains all the fields for some records which are already in the database

the table they are in has a valid field (boolean) which when false effectivly means they are deleted from db (we use this instead of actually deleteing so we can duplicate search against previously held records)

i want to find all the records which are in said excel file and set valid to false

so the two parts of this are pretty simple the update is simple, the select is simple but i need to put them together

 

[fugifox]

so the two parts of this are pretty simple the update is simple, the select is simple but i need to put them together

Then why not using two queries?
Make a Select query to get only the records you want.
Make an Update query based on your previous Select query.

 

[garethl]

was gonna say because i don't know how to do an update based on a select either

i was going to try this ridiculous idea of about 3 temporary tables (selecting out the ones to change and the ones not to change into different tables changing them, then merging together the results and replacing for the old)

but now i think about it its really easy you just mean to use the select query as the source of the update instead of a table and then the records in the table still change i didn't actually realise that would happen!

 

[KeithG]

You need the below format

UPDATE [Your Table Name] INNER JOIN [Your Select Query] ON [Your table Name].[Join Field]=[Your Select Query].[Join Field] Set [Your Table Name].[Field Name]=[New Value]