July 30, 2003 at 4:03 am
Greetings,
Can anyone help?? ANY help would be GREATLY appreciated.
I have a function that uses stuff() to place characters at specific positions within a column. It works when run independently but not as part of an UPDATE
1. I have a table called db.NewArticleCode with the following row:
ArticleCode: RB9212.
strNewArticleCode: RB9212.
2. I have a table called dbo.Option with the following rows
ArticleCode: RB9212.
nStartPos: 8
nLength: 2
ArticleCode: RB9212.
nStartPos: 11
nLength: 2
3. I want to add x's to dbo.NewArticleCode.strNewArticleCode in the positions specified in dbo.Option, dbo.Option.nStartPos tells me where the x's should begin, and dbo.Option.nLength tells me how many x's are required.
From the rows in dbo.Option, dbo.NewArticleCode.strNewArticleCode should end up as: RB9212.XX XX
when I run the following query I end up with: RB9212. XX.
1. It works if I call the function twice manually, using variables instead of tables
2. Works with the UPDATE query the first time I run it in SQL server
3. Stops working correctly after I refresh the dbo.NewArticleCode table. It only seems to apply the last update and not the first.
Can anyone tell me why? ANY HELP WILL BE GREATLY APPRECIATED!
My query is:
UPDATE dbo.NewArticleCode
SET dbo.NewArticleCode.strNewArticleCode = dbo.fnInsertPlaceHolder( dbo.NewArticleCode.strNewArticleCode, [dbo].[option].nStartPos, [dbo].[Option].nLength )
FROM dbo.Article INNER JOIN [dbo].[Option] ON [dbo].[Option].lOptionSetID = dbo.Article.lOptionSetID INNER JOIN dbo.NewArticleCode ON dbo.NewArticleCode.lArticleID = dbo.Article.lArticleID
WHERE [dbo].[Option].nStartPos > 0
fnInsertPlaceHolder contains:
FUNCTION [dbo].[fnInsertPlaceHolder]
(@strArticleCode varchar(100), @iStartPos int, @iLength int)
RETURNS varchar(100)
as
BEGIN
declare @strNewArticleCode varchar(100)
declare @iCurrentLength int
/* set up variable big enough to hold completed article code */
set @strNewArticleCode = replicate( ' ', 100 )
/* get current length of article code & options */
set @iCurrentLength = len( @strArticleCode )
/* put current article code & options into new field */
set @strNewArticleCode = stuff( @strNewArticleCode, 1, @iCurrentLength, @strArticleCode )
/* add the current option to the article code */
set @strNewArticleCode = stuff( @StrNewArticleCode, @iStartPos, @iLength, replicate( 'X', @iLength ) )
return @strNewArticleCode
END
July 30, 2003 at 6:40 am
AS per BOL documentation (Look for Update described in BOL), the update query where in the there is more than one value for update is categorized as undeterministic and hence it says the result is undefined (cannot guarantee which row will be used for update).
Hence one alternative is to use a cursor to loop through all rows in option table and issue update for each row.
July 30, 2003 at 7:35 am
Also if your Option table has no sequencing identifier then whilst select on the table (even for cursor) will probably give the data in the right order but it cannot be guaranteed.
My suggestion would be to put id/sequence number in the Order table starting at one for each ArticleCode code. Then find the max id/sequence, use a while loop to apply each update in turn.
Far away is close at hand in the images of elsewhere.
Anon.
July 30, 2003 at 8:02 am
Thanks for your help. I used a cursor instead and it works a treat.
Thanks.
AS per BOL documentation (Look for Update described in BOL), the update query where in the there is more than one value for update is categorized as undeterministic and hence it says the result is undefined (cannot guarantee which row will be used for update).
Hence one alternative is to use a cursor to loop through all rows in option table and issue update for each row.
[/quote]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply