Update top row

[dptalt]

I want to update the top row in a table.   I'm trying to get this to work: update top (1) mytable set myfield="test" This does not seem to compile.   We have Microsoft SQL Server 2000.   How can I accomplish this?

[Lost User]

AFAIK, the update statement does not support the TOP condition. Try something like this;

UPDATE dbo.myTable
SET myField = 'test'
WHERE myPrimaryKeyField = (SELECT TOP 1 myPrimaryKeyField FROM dbo.myTable)

Hope this helps :)

I are Troll :)

[Geoff Williams]

The predicate "TOP 1" applies only to the SELECT statement, not the UPDATE statement. In order to update just the first row in your table, your UPDATE statement will need to be

UPDATE mytable
SET myfield = "test"
WHERE ((SELECT TOP 1 myfield FROM mytable) = myfield);

(I've split the statement over several lines to improve readability).

[i j russell]

You should have an ORDER BY clause on the subquery to determine the correct TOP 1.

[Not Active]

A more efficient statement would be this.

update Table
set field = new_value
where PK = (select min(PK) from Table)

---

only two letters away from being an asset