My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Impossible!? Update query where duplicates exist in multiple col
|
|
 Aug 15 2004, 01:05 PM
Post
#1
|
|
|
New Member Posts: 8  |
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 |
 |
 
|
|
Aug 15 2004, 02:01 PM
Post
#2
|
|
|
UtterAccess VIP Posts: 13,031 From: Leicester, UK |
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. |
|
|
|
|
Aug 15 2004, 07:48 PM
Post
#3
|
|
|
UtterAccess Addict Posts: 184 From: California |
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. |
|
|
|
|
Aug 15 2004, 09:52 PM
Post
#4
|
|
|
Head Wizard Posts: 14,857 From: South Carolina, USA |
I would eliminate the repeating groups to begin with.
|
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 18th May 2013 - 12:27 PM |