UtterAccess Discussion Forums > Impossible!? Update query where duplicates exist in multiple col

Full Version: Impossible!? Update query where duplicates exist in multiple col

UtterAccess Discussion Forums > Microsoft® Access > Access Queries

Onyaweb2

Aug 15 2004, 01:05 PM

The first part is fine:

CODE

UPDATE TableA INNER JOIN [TableB] ON TableA.[Unique ID] = [TableB].[Unique ID] SET TableA.FieldX = Yes WHERE blah blah

but how do I do the WHERE part..... it should be WHERE there are duplicate values in f2,f3 and f4. In other words the query should update records 01 and 03 only!

f0,f1,f2,f3,f4,f5
01,as,tr,fg,sd,iu
02,as,tr,gf,sd,jh
03,mn,tr,fg,sd,er
04,as,df,fg,sd,jh

dannyseager

Aug 15 2004, 02:01 PM

What about trying to concatanate f2,f3 and f4 into 1 field (in a query).

The the where condition would only have to be 1 field.

ByteMyzer

Aug 15 2004, 07:48 PM

From what I can gather, you are attempting to enumerate records in TableB where fields f2, f3 and f4 have duplicate/matching values, and to set FieldX in the related records in TableA to Yes, am I correct?

If so, this update query might be what you are looking for:

CODE

UPDATE TableA
SET TableA.FieldX=Yes
WHERE TableA.[Unique ID] IN (
    SELECT TB1.[Unique ID]
    FROM TableB AS TB1
    WHERE EXISTS (
        SELECT TB2.f2, TB2.f3, TB2.f4
        FROM TableB AS TB2
        GROUP BY TB2.f2, TB2.f3, TB2.f4
        HAVING TB2.f2=TB1.f2 AND TB2.f3=TB1.f3 AND TB2.f4=TB1.f4 AND Count(TB2.[Unique ID])>1;
    );
);

See if this works for you.

Jerry Dennison

Aug 15 2004, 09:52 PM

I would eliminate the repeating groups to begin with.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.