Been scratching my head at this one and wondering whether anyone else has encountered something like it-- I've been trying to use SAS to update a field in an MS Access data table--let's call it tblFacility. This table has a two-part primary key made up of variables person and site. It is linked to another table, tblIdentifier, which has a three-part primary key made up of person, site and ID. Person and site in tblIdentifier reference person and site in tblFacility, and relational integrity is enforced. I'm trying to update testdate, a date field in tblFacility that does not reference any other table. This is what the relationships look like: Here is my code. ("fi" is a SAS dataset; libname "ptdb" refers to the DSN pointing at the database): PROC SQL;
update ptdb.tblFacility as a
set testdate = (select dhms(b.testdate,0,0,0) as testdate
from fi as b
where b.person = a.person and b.site = a.site and dhms(b.testdate,0,0,0) > dhms(a.testdate,0,0,0))
where exists (
select 1
from fi as b
where b.person = a.person and b.site = a.site and dhms(b.testdate,0,0,0) > dhms(a.testdate,0,0,0))
;
QUIT; When I try to run this, it fails with the following error: ERROR: Error updating table entry: [Microsoft][ODBC Microsoft Access Driver] The record cannot be deleted or changed because table 'tblIdentifier' includes related records. I've found that I can bypass this error by switching on the "cascade update" option for the relationship between these two tables, but I'm frankly mystified as to why it's being raised at all, given that my subquery isn't supposed to be touching the key variables. Am I missing something here? Is there a way to modify this update query to avoid this issue?
... View more
