February 20, 2012 at 2:06 am
Create table #temp(
ID int
,name varchar(20)
,buss_seqno varchar(100) NULL
)
GO
INSERT INTO #temp
SELECT 1,'John',NULL
UNION
SELECT 2,'Bayer',NULL
UNION
SELECT 3,'Chris' ,NULL
select * from #temp
-- I Have a constant for sequence no
DECLARE @start_seqno int
,@rel_seq int
,@rem_val int
--Now for buss_seqno column in # temp table i havve to updated with below logic
SET @start_seqno='12789'
set @rem_val = (@start_seqno % 7)
set @rel_seq=@start_seqno*10+@rem_val
set @start_seqno=@start_seqno+1
--so for each row of temp table i have to update the
UPDATE #temp SET buss_seqno=@rel_seq where ID = @ID
-- In the above update we haeHow to update the #temp table without using while or cursors
February 20, 2012 at 2:13 am
first of all your DDL script is not complete, you reference a column in #temp called tax_ref_id which you have not defined and a variable called @tax_ref_id which again you have not defined
what is your expected result, John is 12789, Bayer is 12790 and Chris is 12791
February 20, 2012 at 2:17 am
Sory for the confudion i have updated the query there is no @tax_ref_id
For each roe of #temp table i have to updated the buss_seqnno
February 20, 2012 at 2:36 am
is there a particular reason you cant use while loops or cursors?
February 20, 2012 at 3:05 am
Something like this?
DECLARE @start_seqno INT
SET @start_seqno='12789';
WITH t1(N) AS (SELECT 1 UNION ALL SELECT 1),
t2(N) AS (SELECT 1 FROM t1 x, t1 y),
t3(N) AS (SELECT 1 FROM t2 x, t2 y),
t4(N) AS (SELECT 1 FROM t3 x, t3 y),
Tally(N) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM t4 x, t4 y)
UPDATE #temp SET buss_seqno=new.buss_seqno
FROM (SELECT ID, name, new.buss_seqno
FROM (SELECT ID, name, buss_seqno, ROW_NUMBER() OVER (ORDER BY ID) AS rn
FROM #temp) old
INNER JOIN (SELECT start_seqo*10+rem_val AS buss_seqno, N+1 AS N
FROM Tally
CROSS APPLY (SELECT N+@start_seqno AS start_seqo) b
CROSS APPLY (SELECT start_seqo % 7 AS rem_val) c) new ON old.rn = new.N) new
WHERE #temp.ID = new.ID
February 20, 2012 at 4:50 am
Better way: -
UPDATE #temp SET buss_seqno=new.buss_seqno
FROM (SELECT ID, name, start_seqo*10+rem_val AS buss_seqno
FROM (SELECT ID, name, buss_seqno, ROW_NUMBER() OVER (ORDER BY ID) - 1 AS rn
FROM #temp) a
CROSS APPLY (SELECT rn+@start_seqno AS start_seqo) b
CROSS APPLY (SELECT start_seqo % 7 AS rem_val) c) new
WHERE #temp.ID = new.ID
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply