We help IT Professionals succeed at work.

SQL Update Criteria

coperations07
on
Hi,
I'm trying to create an update query that will update a table in Access. I want to flag all the records that have duplicate values in one of the columns. The flag will be a 'N' value in a corresponding column for a dupe record. I have two queries that I've been trying to make work together, but no luck so far.
To set the flag:
UPDATE tbl_os_reverse_save SET tbl_os_reverse_save.FILL = Left([tbl_os_reverse_save]![FILL],Len([tbl_os_reverse_save]![FILL])-1) & 'N'

To find the dupes:
SELECT First(tbl_os_reverse_save.[WHLSL AGIN]) AS [WHLSL AGIN Field], Count(tbl_os_reverse_save.[WHLSL AGIN]) AS NumberOfDups
FROM tbl_os_reverse_save
GROUP BY tbl_os_reverse_save.[WHLSL AGIN]
HAVING (((Count(tbl_os_reverse_save.[WHLSL AGIN]))>1))
Comment
Watch Question

Your first query is going to impact every row in the table, not just the dup erecords.
I'm not at all sure what you are trying to achieve with the second query.  What is your expectation from your use of FIRST in that query.
I am also not at all sure why you have asked this in a SQL Server forum. :-/
I am going to assume, since you mention updating a table in Access and because of your attempt to use FIRST (which is not a SQL Server function), that you want to do this in Access.
So, open the Create Query Wizard in ms Access and select the Find Duplicates option for creating a query.  Specify your [WHLSL AGIN] column as the one you want to use to define duplicates and then include the ID column (or the columns for the PK) in the data to be returned.
Now, join your [tbl_os_reverse_save] table with that query as a SELECT statement and SELECT the [WHLSL AGIN] column in this query.  Turn the query into an UPDATE query and put your update expression in the Udate To area under that column. now save this as a named query.
That should do it. ;-)

Author

Commented:
That's what I needed. Thanks!