updating one row out of multiple rows 1

[henin]

Hi everyone,
I wrote a script to update a table as follows:

IF EXISTS(SELECT * FROM contsupp WHERE accountno='95376')
  BEGIN
    UPDATE contsupp
    SET recommend='2'
    WHERE
    accountno='95376'
  END
ELSE
  BEGIN
    INSERT INTO contsupp
    (accountno,recid,recommend)
    SELECT
    '1','1','1'
  END

The problem is: there are multiple records with accountno:'95376'. I want only one row to be updated, say, that with maximum date value ("mydate" column). Can any one tell me how to do that?
Thanks

 

[LNBruno]

Try doing a "Search" on "maximum date value"

< M!ke >
[small]"Oops" is never a good thing.[/small]

 

[SQLDenis]

<M!ke>,

That is cruel and unusual punishment! You know the search doesn't work ;-)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[LNBruno]

Sure it does...I tried it just before posting to make sure.

Timing is everything, though! ;-)



< M!ke >
[small]"Oops" is never a good thing.[/small]

 

[henin]

Thanks a lot M!ke, great punishment !