question

David 2 1 avatar image
David 2 1 asked

Update Column In Table With Numeric Values Generated From A Total Count Of That Table

How can I create a procedure that will update a column by an equal split for the total number of rows in the table. For example this is what I have so far:

This is the test table:

CREATE TABLE test_batch(                    
client INT,                    
phone NUMERIC(11),                    
name VARCHAR(10),                    
batch CHAR(1))                    

This is the test data:

INSERT INTO test_batch(client, phone, name) VALUES(1,11111111111,'David')                    
INSERT INTO test_batch(client, phone, name) VALUES(2,22222222222,'Harry')                    
INSERT INTO test_batch(client, phone, name) VALUES(3,33333333333,'Steve')                    
INSERT INTO test_batch(client, phone, name) VALUES(4,44444444444,'Fred')                    
INSERT INTO test_batch(client, phone, name) VALUES(5,55555555555,'Jane')                    
INSERT INTO test_batch(client, phone, name) VALUES(6,66666666666,'Julie')                    

This is the test procedure:

CREATE PROCEDURE test_batch_proc @tabname VARCHAR(50), @num CHAR(1)                    
AS                    
SET NOCOUNT ON                    
                    
DECLARE @split VARCHAR(8000)                    
                    
SELECT @split = '                    
                    
DECLARE @client varchar(10)                    
DECLARE @phone varchar(20)                    
DECLARE @name varchar(75)                    
DECLARE @batch char(1)                    
                    
DECLARE cur_1 CURSOR FAST_FORWARD FOR                    
SELECT CLIENT, PHONE, NAME, BATCH FROM ' + @tabname + ' WHERE BATCH IS NULL                    
                    
OPEN cur_1                    
FETCH NEXT FROM cur_1                    
INTO @client, @phone, @name, @batch                    
WHILE @@FETCH_STATUS = 0                    
BEGIN                    
UPDATE ' + @tabname + '                    
SET BATCH = ' + @num + '                    
WHERE BATCH IS NULL                    
FETCH NEXT FROM cur_1                    
INTO @client, @phone, @name, @batch                    
END                    
CLOSE cur_1                    
DEALLOCATE cur_1'                    
                    
EXEC (@split)                    

When executing the procedure I want the batch column in the table to be updated with a number as if split evenly. For example in my test table above if I specify 3 when the procedure is execute I want to update 2 rows of the batch column with the values '1', 2 rows with '2', and 2 rows with '3'. Basically I want the total sum of the tables rows split into an equal number of rows by the number specified at the procedure point.

EXEC test_batch_proc test_batch, 3                     

Thanks in advance.

t-sqlsql-server-2000
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Liam avatar image
Liam answered

--try this

declare @split int            
declare @splitcount int            
    declare @loopcounter int            
set @split = 3            
            
            
set @splitcount = (            
                select floor(count(*)/@split)            
                from test_batch            
                where batch is null            
                    )            
            
            
set @loopcounter = 0            
while @loopcounter <= @split            
begin            
set @loopcounter = @loopcounter + 1            
            
            
update top (@splitcount) test_batch             
set batch = @loopcounter            
where batch is null            
            
end            

--I'm not sure what you want to do with the odd ones that cannot be split perfectly so currently assigned to additional 'ghost' person

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.