SQL query to update top 1 record in table

Can anybody please tell me how开发者_如何学C to write query to update top 1 record in table ?

Thanks

YOU have to decide what the top record is in a table, by ordering against a column you decide on.

That said, you could do this in SQL Server:

UPDATE [YourTable]
SET [YourColumn] = SomeValue
WHERE [PrimaryKey] IN 
(
   SELECT TOP 1 [PrimaryKey]
   FROM [YourTable]
   ORDER BY [PrimaryKey]  -- You need to decide what column you want to sort on
)

There is no "top 1 record" in a relational table. Records are not in any order unless you specify one in the query.

in SQLServer you can use the TOP keyword in your update expression:

http://msdn.microsoft.com/en-us/library/ms177523.aspx

When a TOP (n) clause is used with UPDATE, the update operation is performed on a random selection of 'n' number of rows.

In MS SQL, in addition to the existing answers, it is possible to UPDATE the TOP N rows returned through a Common Table Expression (CTE). You are able to define an ORDER BY if required, as well.

One advantage of this is that it will work even if your table doesn't have a primary key:

WITH Top1Foo AS
(
    SELECT TOP 1 *
    FROM Foos
    ORDER BY Name DESC
)
UPDATE Top1Foo
  SET Name = 'zzz';

There's a Example SqlFiddle here

However, the caveat mentioned by other users remains - using TOP implies there are more records meeting the selection criteria, and it risks updating an arbitrary record / records.