CODEWITHSUNDEEP
sqlms-access
Robbo
Robbo

Reputation: 87

SQL UPDate same table

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

Upvotes: 0

Views: 129

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123849

I believe you are looking for something like this:

UPDATE [IO Documents] SET
codedFileName = DMin("codedFileName","IO Documents","LegacyFullPathName='" & LegacyFullPathName & "'")

Upvotes: 1

Steven
Steven

Reputation: 240

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).

Upvotes: 1

Related Questions