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 syntax problems

I am working in vb6 and am new to database access. I can't seem to figure out the syntax for the following:

rs1.Open "select * from orders where category = '" & button & " and invoice = " & invoice & "' order by guest", db, adOpenStatic, adLockOptimistic

The above statement doesn't work. What am I doing wrong?

JerrybYour embedded quotes were misplaced. The SQL being run is like:

select * from orders where category = 'xxx and invoice = yyy ' order by guest

But you probably meant this:

select * from orders where category = 'xxx' and invoice = 'yyy' order by guest

Try this:

rs1.Open "select * from orders where category = '" & button & "' and invoice = '" & invoice & "' order by guest", db, adOpenStatic, adLockOptimistic

(This is assuming that both button and invoice are strings rather than numbers - for numbers, you don't need the quotes.)

Hint: it's a good idea to print the SQL that is failing to see if it makes sense - e.g.:

Response.Write "select * from orders where category = '" & button & " and invoice = " & invoice & "' order by guest"