Update the first occurence ONLY

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update the first occurence ONLY

Author

Topic

Neal44
Starting Member

17 Posts

Posted - 2010-06-18 : 10:47:36

Hi,

The code below is part of a stored procedure. I need to update the first occurence ONLY - using the minimum value of S.Test_Sequence where S.Result is either blank or null (S.Test_Sequence needs to be added to the code).

UPDATE dbo.LSR_Prepare_Samples_Temp
SET 
Result = R.Result,
Date_Received = R.Date_Reviewed,
Date_Started = R.Date_Reviewed,
Date_Completed = R.Date_Reviewed,
Date_Reviewed = R.Date_Reviewed,
Third_Party_Id = R.Third_Party_Id
FROM dbo.LSR_Prepare_Samples_Temp S LEFT JOIN dbo.LSR_Prepare_Build_Results_Temp R ON
R.AB_Id = S.AB_Id AND
R.A_Code = S.A_Code 
WHERE 
(R.PK_Id = @RowNum
AND
S.Result IS NULL)
OR
(R.PK_Id = @RowNum
AND
RTRIM(LTRIM(S.Result)) = '')

Notes:
@RowNum is an incremented cursor
The Test_Sequence (smallint) is part of the dbo.LSR_Prepare_Samples_Temp primary key.

dbo.LSR_Prepare_Samples_Temp
AB_Id, A_Code, Result, Test_Sequence
1212, A1AA, Null, 1
1212, A1AA, Null, 2
1212, A1AA, Null, 3
1728, A1AA, 2.3, 1
1728, A1AA, Null, 2

in the case of record 1212, A1AA the next update would need to be where Test_Sequence = 1
in the case of record 1728, A1AA the next update would need to be where Test_Sequence = 2

Any ideas please?

Thanks in advance,

Neal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-06-18 : 12:31:32

you mean update only first occurance of null value for each AB_Id?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-06-18 : 12:37:21

[code]
UPDATE t
SET t.Result=value
FROM(SELECT Result,ROW_NUMBER() OVER (PARTITION BY AB_Id, A_Code ORDER BY Test_Sequence) AS Rno FROM Table)t
WHERE Rno=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Neal44
Starting Member

17 Posts

Posted - 2010-06-20 : 04:54:17

Thank you.

Yes update where first occurence of Null value

Neal

niechen861102
Starting Member

9 Posts

Posted - 2010-06-20 : 22:55:05

Test_Sequence where S.Result is either blank or null (S.Test_Sequence needs to be added to the code).

spam removed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources