Query to update 1 record in a duplicate set of records

How do I update a record that has duplicates. For example, I have 3612 orders some of these orders have multiple orderid's I want to update the record for each of these orders that was added most recently.

Without more info it's hard to tell, but you just need to qualify what you want to update:

UPDATE Table
SET column = 'New Value'
WHERE column = 'youNeedADateHere'

AND orderid = 'Whatever'
GO

|||Thanks for your response but I can't specify an orderid because there are 3612 records and within those 3612 records some of the id's are dups. The query above would do a specific orderid and a specific date. The dates are all different I just want the most recent per orderid. Thank you anyway.|||

Something along the lines of:

UPDATE Table1

SET NewCol = 1

FROM Table1 AS i

INNER JOIN (SELECT OrderID, COUNT(*) AS c, MAX(OrderDate) AS OrderDate

FROM Table1

GROUP BY OrderID

HAVING COUNT(*) > 1) AS Dupes

ON i.OrderID = Dupes.OrderID

AND i.OrderDate = Dupes.OrderDate

|||Thank you that is close enough to what I was looking for. I appreciate your response

query to show duplicates

mytable fld1 int primkey
fld2 varchar(20),
fld3 varchar(20)
From the definition of the above table, how do i do i modify my below query to only select the rows with duplicates in fld2. I know a groupby with a having count will display the duplicates for a given field, but i want my query to see all the fields and rows that are duplicates.
select fld1, fld2, fld3 from mytable
SELECT fld2,COUNT(*) FROM myTable GROUP BY Flt2 HAVING COUNT(*)>1

Query To Find Duplicates

Hi

I am trying to find when a name has been entered more than once into 1 database table.

I'm currently doing something like this (can't remember exactly, not at work)

SELECT COUNT(*) AS Cnt, Name
FROM tblTable
GROUP BY Name
ORDER BY Cnt Desc

This brings back all the Names in the database and tells me which are duplicates but I want to just have the results of the duplicate values and not the single values.

Hope you can help.

ThanksWHERE cnt > 1|||Having Count(*) > 1

Query to filter duplicate data from table.

i am having 3000 records in table. now i want take out the duplicate from that table.
for example: i want to find out the duplicates of 'CompanyNames'.
help needed to write query for this operation.Originally posted by pln_verma
i am having 3000 records in table. now i want take out the duplicate from that table.

for example: i want to find out the duplicates of 'CompanyNames'.

help needed to write query for this operation.

check this :
http://www.sqlteam.com/item.asp?ItemID=3331|||Does your table have a surrogate key or any method of uniquely identifying a record?

Query to extract number of duplicates

This query is driving me insane.
I need to extract the number of rows in tblUsers where the first 3
characters of "fldSurname" are the same and they where born "fldDOB" on the
same year and month.
Can anyone help ?Poppy,
Try this link from MS!
I had to use it the other w to remove 150,000 duplicate rows from a live
database and it worked a treat.
You need to modify the SQL slightly to accomodate your data, but the logic
is good and should help you out.
Thanks Microsoft! :)
Immy
"poppy" <poppy@.discussions.microsoft.com> wrote in message
news:F7BE4539-D96B-4FDF-AD48-188649EC4CF9@.microsoft.com...
> This query is driving me insane.
> I need to extract the number of rows in tblUsers where the first 3
> characters of "fldSurname" are the same and they where born "fldDOB" on
> the
> same year and month.
> Can anyone help ?|||if all you need is the count:
select left(fldSurname,3) as surname_start, fldDOB, count(*) as rows
from tblUsers
group by left(fldSurname,3), fldDOB
poppy wrote:
> This query is driving me insane.
> I need to extract the number of rows in tblUsers where the first 3
> characters of "fldSurname" are the same and they where born "fldDOB" on th
e
> same year and month.
> Can anyone help ?|||What link from MS '
"Immy" wrote:

> Poppy,
> Try this link from MS!
> I had to use it the other w to remove 150,000 duplicate rows from a liv
e
> database and it worked a treat.
> You need to modify the SQL slightly to accomodate your data, but the logi
c
> is good and should help you out.
> Thanks Microsoft! :)
> Immy
> "poppy" <poppy@.discussions.microsoft.com> wrote in message
> news:F7BE4539-D96B-4FDF-AD48-188649EC4CF9@.microsoft.com...
>
>|||DOH!!!! God damn CTRL+C! :)
Here it is...
Just note that this will help to remove the records also...
If you want to just see how many you have then a simple count as suggested
by Trey will show the results.
http://support.microsoft.com/defaul...kb;en-us;139444
Regards
Immy
"poppy" <poppy@.discussions.microsoft.com> wrote in message
news:AFCD89C6-2862-4304-B554-0581E085E78C@.microsoft.com...
> What link from MS '
> "Immy" wrote:
>