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.
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_TempSET 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_IdFROM dbo.LSR_Prepare_Samples_Temp S LEFT JOIN dbo.LSR_Prepare_Build_Results_Temp R ONR.AB_Id = S.AB_Id ANDR.A_Code = S.A_Code WHERE (R.PK_Id = @RowNumANDS.Result IS NULL)OR(R.PK_Id = @RowNumANDRTRIM(LTRIM(S.Result)) = '') Notes:@RowNum is an incremented cursorThe Test_Sequence (smallint) is part of the dbo.LSR_Prepare_Samples_Temp primary key.dbo.LSR_Prepare_Samples_TempAB_Id, A_Code, Result, Test_Sequence1212, A1AA, Null, 11212, A1AA, Null, 21212, A1AA, Null, 31728, A1AA, 2.3, 11728, A1AA, Null, 2in the case of record 1212, A1AA the next update would need to be where Test_Sequence = 1in 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 MVPhttp://visakhm.blogspot.com/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-06-18 : 12:37:21
|
[code]UPDATE tSET t.Result=valueFROM(SELECT Result,ROW_NUMBER() OVER (PARTITION BY AB_Id, A_Code ORDER BY Test_Sequence) AS Rno FROM Table)tWHERE Rno=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Neal44
Starting Member
17 Posts |
Posted - 2010-06-20 : 04:54:17
|
Thank you.Yes update where first occurence of Null valueNeal |
|
|
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 |
|
|
|
|
|
|
|