Not as simple a task as one might imagine (as I'm sure you have worked out). The initial selection would be easy enough as you could simply query the sample table for items with nothing in the LOCATION_KEY field (in SQL Server tools).

I would have thought though that the harder task would be to check for whether the sample spatial ref falls entirely within a polygon. A potentially very long piece of GIS data crunching, beginning with creation of a layer of all the records with square polygons reflecting their spatial ref precision, then performing "entirely within" query, using the results of that query to populate the sample location key in SQL Server tools again. What GIS system do you use?

I would have to ask what you hope to gain by it and whether you think it is really worthwhile. Might it not create a false sense of the reliability of the data? On our system for example we have hundreds of thousands of tetrad records which would not be attributable to any individual site (except in rare circumstances) even though they were in many cases recorded on accessible areas of species-rich land which we recognise as sites in our GIS layers, so if we wanted to query all of the records for a given site and be sure of getting them all, we still need to query all records which overlap and are thus potentially within the site. The situation is further complicated when site boundaries are edited from time to time. If a site is extended you would then have to requery and add any which fall within the new boundary.

I have given this a lot of thought over the years and always end up deciding that the only time we will associate an occurrence with a location, is when we can be sure that the recorder was familiar with the currently recognised boundary of the site and explicitly stated that it was within the site. Consider the situation where a recorder makes a minor error in the grid reference, giving a value which is inside a site rather than just outside. The spatial error is relatively inconsequential, moving the record by a few metres perhaps, but such post processing  as you propose creates a larger conceptual error by positively identifying a record as falling within the site, when it in fact does not.

I suppose, given that you work for NT it is possible that your dataset only contains data which were recorded within your sites though, which changes things somewhat.

Not sure if that helps you all that much though.

On reflection I think I will move all the relevant samples into a survey set aside for them and work on them there. Does this code look like it will do the job:

update     SURVEY_EVENT set  SURVEY_EVENT.SURVEY_KEY ="DSS000460000004J"
FROM         SAMPLE INNER JOIN
                      SURVEY_EVENT ON SAMPLE.SURVEY_EVENT_KEY = SURVEY_EVENT.SURVEY_EVENT_KEY
WHERE     (SAMPLE.LOCATION_KEY IS NULL)

Your update query has the wrong syntax I think, try this

UPDATE SAMPLE INNER JOIN SURVEY_EVENT ON SAMPLE.SURVEY_EVENT_KEY = SURVEY_EVENT.SURVEY_EVENT_KEY
SET SURVEY_EVENT.SURVEY_KEY = "DSS000460000004J"
WHERE (((SAMPLE.LOCATION_KEY) Is Null))

Although I don't think I would bother moving them to another survey, they will be impossible to put back afterwards.

By the way it is always good practise to update the changed_by and changed_date fields when you do this (and perhaps the comment too)