0

I know this has been posted before but I am not sure I have got my head around the logic let aloan trying to get it into to JET Friendly Syntax.

Here is what I am trying to do

I have a bunch of records that relate to documents and I am planning on renaming the documents with GUID's however some records point to the same document here lays the problem.

Table ID, LegacyFullPathNme, GUID, isDuplicate

my code loops through and assigns each record a GUID. then I want to update the Duplicate Documents records with the same GUID

below is my hash at it but doesn't work "Operation must use an updateable Query

UPDATE [IO Documents] a
set a.codedFileName = (SELECT B.codedFileName
                       FROM  [IO Documents] b
                       WHERE b.LegacyFullPathName  = a.LegacyFullPathName)

Currently use a macro to go throw RBAR

Robbo
  • 87
  • 1
  • 2
  • 6
  • possible duplicate of [Operation must use an updatable query. (Error 3073) Microsoft Access](http://stackoverflow.com/questions/170578/operation-must-use-an-updatable-query-error-3073-microsoft-access) – Mack Dec 02 '13 at 17:19

2 Answers2

1

I'm a little confused on why you would do it this way since now your globally unique id column isn't unique in that multiple rows will have it.

I think a better method would be to simply create a new table from your old one with a row for each file path.

SELECT LegacyFullPathNme
INTO newtable
FROM oldtable
GROUP BY LegacyFullPathNme;

and then add the guid into the new table afterwards. (note that I didn't test that sql snippet so that might not be proper syntax but I think it gets the point across).

Steven
  • 240
  • 1
  • 7
  • thank you this is really nice solution, and after reading I have no idea how I have made it so overly complex. – Robbo Dec 02 '13 at 17:30
  • No problem. Sometimes a problem just needs a new set of eyes. Good luck. – Steven Dec 02 '13 at 17:37
1

I believe you are looking for something like this:

UPDATE [IO Documents] SET
codedFileName = DMin("codedFileName","IO Documents","LegacyFullPathName='" & LegacyFullPathName & "'")
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418