January 29, 2014 at 8:45 pm
I have two tables.
Table1 structure is
SetId (PK, int identity(1, 1))
MsgCount (int)
Table2 structure is
Id (PK, int identity(1, 1))
SetId (int default 0)
MsgText (nvarchar)
The requirement is the Table1.SetId (1, 2, 3...) is to be assigned to the Table2.SetId for the same number of rows as the value of Table1.MsgCount. For example,
Table1 Sample Data
SetId, MsgCount
1, 3
2, 2
3, 1
4, 2
Table2 Sample Data
Id, SetId, MsgText
1, 0, Message1
2, 0, Message2
3, 0, Message3
4, 0, Message4
5, 0, Message5
6, 0, Message6
7, 0, Message7
8, 0, Message8
9, 0, Message9
10, 0, Message10
Using the above example data sets, the result of the update should look like this
Table2 after update
Id, SetId, MsgText
1, 1, Message1
2, 1, Message2
3, 1, Message3
4, 2, Message4
5, 2, Message5
6, 1, Message6
7, 2, Message7
8, 2, Message8
9, 0, Message9
10, 0, Message10
The goal is to do this in one UPDATE statement (no looping). I'm thinking a recursive CTE might work but am not sure how it would be implemented.
Any suggestions?
Thanks.
January 30, 2014 at 6:13 am
hi
u have specified not use join and also loop but as per my knowledge we have to use either one concept, so i used join try with this
declare @Temp1 table
(
ID int identity (1,1),
msgcnt int
)
declare @Temp2 table
(
ID1 int identity (1,1),
SETID INT DEFAULT 0,
msgTEXT VARCHAR(MAX)
)
INSERT INTO @Temp1
vALUES (3),(2),(1),(2)
INSERT INTO @Temp2(msgTEXT)
vALUES ('MSG1'),('MSG2'),('MSG3'),('MSG4'),('MSG5'),('MSG6'),('MSG7'),('MSG8'),('MSG9'),('MSG10'),('MSG11'),('MSG12'),('MSG13')
Create Table #RecCont
(
ID1 int identity (1,1),
SETID INT DEFAULT 0,
msgTEXT int
)
insert into #RecCont
select b.ID 'ParentID',b.msgcnt
from @Temp1 a ,
@Temp1 b
where a.ID<=b.msgcnt
update b
set b.SETID = a.SETID
from #RecCont a ,
@Temp2 b
Where a.ID1 = b.ID1
select * from @Temp2
drop table #RecCont
thanks
Vijay
January 30, 2014 at 6:39 am
Borrowing Vijay's fine setup code for the sample data...
Here is a multiple CTE version, with no explicit temp tables (but the possibility of spilling into tempdb for large data sets)
with tally(n) as
(
select row_number() over(order by (select 1)) as n
from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) a(a)
, (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) b(b)
, (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) c(c)
), links as
(
select id,row_number() over(order by id,tally.n) as m
from @temp1
join tally
on n<=msgcnt
), target as
(
select id1,setid,msgtext,[highlight="#ffff11"]row_number() over(order by id1) as l[/highlight]
from @temp2
)
update target
set target.setid = links.id
from links
join target
on target.l = links.m
select *
from @temp2
The inline tally her can manage up to 1000 msg per ID, just add more cross joins for larger "msgcnt" values, or take some away for smaller values.
Also, I have used row_number() to ensure we update the first 8 rows in the target table, but if the ID can be relied upon, you could remove the highlighted row_number()s to this:
with tally(n) as
(
select row_number() over(order by (select 1)) as n
from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) a(a)
, (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) b(b)
, (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) c(c)
), links as
(
select id,row_number() over(order by id,tally.n) as m
from @temp1
join tally
on n<=msgcnt
)
update target
set target.setid = links.id
from links
join @temp2 target
on target.id1 = links.m
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
January 30, 2014 at 6:58 am
-- The requirement is the Table1.SetId (1, 2, 3...) is to be assigned to the Table2.SetId
-- for the same number of rows as the value of Table1.MsgCount. For example,
CREATE TABLE Table1 (SetId int identity(1, 1) PRIMARY KEY,MsgCount int)
CREATE TABLE Table2 (Id int identity(1, 1) PRIMARY KEY,SetId int default 0,MsgText nvarchar (100))
SET IDENTITY_INSERT Table1 ON
INSERT INTO Table1 (SetId, MsgCount)
VALUES (1, 3),(2, 2),(3, 1),(4, 2)
SET IDENTITY_INSERT Table1 OFF
SET IDENTITY_INSERT Table2 ON
INSERT INTO Table2 (Id, SetId, MsgText)
VALUES
(1, 0, 'Message1'),(2, 0, 'Message2'),(3, 0, 'Message3'),(4, 0, 'Message4'),(5, 0, 'Message5'),
(6, 0, 'Message6'),(7, 0, 'Message7'),(8, 0, 'Message8'),(9, 0, 'Message9'),(10, 0, 'Message10')
SET IDENTITY_INSERT Table2 OFF
---------------------------------------------------------------------------------------------
UPDATE t2 SET Setid = d.MsgCount
FROM Table2 t2
INNER JOIN (
SELECT t1.MsgCount, ID = ROW_NUMBER() OVER(ORDER BY SetID)
FROM Table1 t1
CROSS APPLY (VALUES (1),(2),(3),(4),(5),(6),(7)) y (n) -- use your tally table of choice
WHERE y.n <= t1.MsgCount
) d ON d.ID = t2.id
SELECT * FROM Table2
Oops! MM beat me to it 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 30, 2014 at 7:15 am
I was interested in this post as I currently have a cursor that does this and have been looking for a way to replace it.
I have asked in other forums for help but did not explain the problem as well as John.
I hope with these examples to be able to replace my cursor.
January 30, 2014 at 7:54 am
I should have mentioned there is a numbers table (referred to as a tally table here) in the database already. I'll modify the examples to use that instead.
My initial tests are indicating the above suggestions will work. Now to put it into action with the real data. I'll post an update
Thank you all for the contributions!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply