Update Query 1

[zemp]

SQL Server 2000 SP4

I am trying to create a Query to update one field from all records (100's of records) in a particuler table. So far I have only been successful in updating one record at a time. Here is what I have so far,

declare @CID varchar(20)

set @CID = (select (dbo.udfFormatNum(annumber,1 )) from cfautonumber where anid=1)
UPDATE cfautonumber SET annumber = annumber + 1 where anid=1

Update maGroup set clientID=@CID where grpid=1

By changing the grpid I can update a specific record.

I was hoping to get something similar to this

Update maGroup set clientID=(
	(select dbo.udfFormatNum(annumber,1 ) from cfautonumber where anid=1)
	UPDATE cfautonumber SET annumber = annumber + 1 where anid=1
	)

but I keep getting syntax errors.

Can this be done and if so how?


zemp

 

[AlexCuse]

What exactly are you trying to do here? I see you are trying to update clientID, but it is unclear to me what the relationship is between the maGroup table and cfautonumber table.


Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[zemp]

There is no direct relationship. The cfAutonumber table is used toi store user defined numbers that can be added automatically.

All the two statements do is to get the next user defined number, format it with the function, and then increment it so the same number is not used again.

All I am trying to do is to take this formatted number (a string because it can contain an alpha prefix) and insert it into the maGroup.ClientID field.

zemp

 

[AlexCuse]

I see. If it doesn't matter which number goes where, I think you will be able to do something like take the first record where ClientID is null, and set it to the minimum unused number on cfAutoNumber. Because there is no way to tell the server which record to put where, I think you would be best off to write a loop and repeat your single update statement (the one that worked) for each record.

This is a good one, please post when you find the solution.

HTH,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[zemp]

Resolved!

After looking Alex's suggestion (thanks Alex) of writing a loop I came up with the following T-SQL statement that works.

-- Declare variables
DECLARE @CID varchar(20)  --Formated Autonumber.
DECLARE @RTN_Record int   --Active record.

--Open a cursor based on the recordset to be updated.
DECLARE Rtn_Cursor CURSOR FOR
   SELECT GrpID FROM maGroup Order BY GrpID
OPEN RTN_Cursor

--Fetch the next logical record (first one in this case).
FETCH NEXT FROM RTN_Cursor INTO @RTN_Record

BEGIN
  WHILE @@FETCH_STATUS = 0  --Check that the record exists.

    BEGIN
       --Set the formatted user defined auto number with a function.
       SET @CID = (SELECT (dbo.udfFormatNum(ANNumber,1 )) FROM cfAutoNumber WHERE ANID = 1)
       --Update the autonumber table to be ready for the next usage.
       UPDATE cfAutoNumber SET ANNumber = ANNumber + 1 WHERE ANID = 1
       --Place the auto number in the currect record.
       UPDATE maGroup SET ClientID = @CID WHERE GrpID = @RTN_Record
			
       --Fetch the next logical record.
       FETCH NEXT FROM RTN_Cursor INTO @RTN_Record
    END

END

CLOSE RTN_Cursor
DEALLOCATE RTN_Cursor

For those interestd I have posted the Function below.
FYI the user can enter a prefix for the autonumber (up to 4 characters) and they can select a length (4 - 16 characted,even numbers only). So if the user entered "CL" (for Client) for the prefix and select a length of 6 digits for the actual number then the first formatted auto number would be "CL000001".

CREATE FUNCTION udfFormatNum (@Num int, @ANID int) 

RETURNS varchar(20) 

AS 

BEGIN 

DECLARE @New varchar(20) 
DECLARE @Len tinyint 
DECLARE @Pfix varchar(4)

SET @len = (SELECT  anLength FROM cfAutoNumber WHERE ANID = @ANID) 
SET @Pfix = (SELECT anPrefix FROM cfAutoNumber WHERE ANID = @ANID) 

SET @New = @Pfix + RIGHT('0000000000000000' + CONVERT(varchar, @Num),@len) 

RETURN(@New) 

END

zemp