[thelist] SQL 2000 update statement

[Ashok at MagicalKenya.com]

>Currently, am using this...
>Update ProdSummary Set TheTotalOrder = @total where LastName = @last
>
>I want to update only ONE row that has that LastName..

>From what i can guess you have multiple rows in the table that have
the same LastName key, but you would like to update only a single row....

I hope you have an additional unique identifier on each row containing
sale information...

If you dont maybe this could help you :

if your update is done in a stored procedure , you can use the SQL Server
'set rowcount' property to update a single row...

for example :
if your table is like this :

last-name value
--------- ------
smith          50
smith          20
johnson   30
jameson   20

normally an update like :

update table set value=100 where last-name='smith'

will update both the 'smith' rows to the value 100...

BUT if you do this within a stored procedure, with a set rowcount:

create procedure update_proc
as
begin
--....code....
Set rowcount 1 -- set sql rowcount to 1
update table set value=100 where last-name='smith'
Set rowcount 0 -- reset rowcount to all
--....more code....
end

the command 'set rowcount 0' instructs sql server to return/update
just a single row of any subsequent query....
this procedure above will update only a single row containing the last-name
'smith' ,

the difficult question for you now is , how do you know which 'smith' the
query has updated...?!

I used Set Rowcount in this way a long time back with SQL Server 4.2 to
simulate a sequential
cursor....(they didnt have t-sql cursors in that version...)