Update 1 of multiple records

[bminaeff]

Hello All,

I have an application where I am fed some data by some equipment and log this data to a table. One of my inputs is a unique number which allows me to identify a record, but it is not a primary key because under certain circumstances my input may be a constant = '?????'. So I may get a table that looks like :

Num1 | Num2 | Num3 |Timestamp|
-------------------------------------
12345 |123 | 123 | 11:30 AM
????? |??? | ??? | 11:31 AM
67890 |456 | ??? | 11:32 AM
????? |??? | ??? | 11:33 AM

What I would like to do is have a stored procedure that would allow me to input Num1, Num2, Num3 and update a record based on Num1. So for instance I count update Num3 for the '67890' record. I would also like the stored procedure to update 1 of the '?????' records when @@RowCount = 0, but I need to keep the count correct. How can I update only 1 record when really there is nothing unique about them? The timestamp is the only thing that really tells me there were 2 entry's when this occurs.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[Reconcile]
@Num1 nvarchar (50),
@Num2 nvarchar(50),
@Num3 nvarchar(50)

AS
BEGIN
      SET NOCOUNT ON;
       DECLARE	@SortStamp smalldatetime
       SET @SortStamp=  GetDate()

    -- Insert statements for procedure here
      Select * from dbo.Details where Num1= @Num1
      If @@RowCount >0 
      BEGIN
      UPDATE dbo.Details SET Num2= @Num2, Num3= @Num3, TimeStamp = @TimeStamp where Num1= @Num1
      END   
      ELSE  
-- Need to update 1 record where Num1 = '?????'
          
END
[\code]

Any help would be appreciated.

 

[RiverGuy]

To update just one of the records, you can use SET ROWCOUNT 1, although if you are saying there is at least one column which aids in making the record unique (you mentioned the timestamp was different), I would probably update based on that, by selecting the MIN or MAX, and adding that to your WHERE clause.