Tricky String Update
-
Hello all... I got a tricky one that I hope someone can help with.
I am trying to update the first occurace of a document listed in the table that has multiple pages. Here is the before:
AutoID Document Page PageNum PagesInDoc 1 WPELL-0000001 WPELL-0000001 1 2 WPELL-0000002 WPELL-0000002 1 3 WPELL-0000003 WPELL-0000003 1 4 WPELL-0000004 WPELL-0000004 1 5 WPELL-0000004 WPELL-0000005 2 6 WPELL-0000006 WPELL-0000006 1 7 WPELL-0000007 WPELL-0000007 1 8 WPELL-0000008 WPELL-0000008 1 9 WPELL-0000009 WPELL-0000009 1 10 WPELL-0000010 WPELL-0000010 1 11 WPELL-0000011 WPELL-0000011 1 12 WPELL-0000012 WPELL-0000012 1 13 WPELL-0000013 WPELL-0000013 1 14 WPELL-0000013 WPELL-0000014 2 15 WPELL-0000013 WPELL-0000015 3 16 WPELL-0000013 WPELL-0000016 4 17 WPELL-0000013 WPELL-0000017 5 18 WPELL-0000018 WPELL-0000018 1 19 WPELL-0000019 WPELL-0000019 1 20 WPELL-0000019 WPELL-0000020 2 21 WPELL-0000019 WPELL-0000021 3 22 WPELL-0000022 WPELL-0000022 1 23 WPELL-0000022 WPELL-0000023 2 24 WPELL-0000024 WPELL-0000024 1 25 WPELL-0000025 WPELL-0000025 1 I would like it to update Pages in Doc as:
AutoID Document Page PageNum PagesInDoc 1 WPELL-0000001 WPELL-0000001 1 WPELL-0000001 2 WPELL-0000002 WPELL-0000002 1 WPELL-0000002 3 WPELL-0000003 WPELL-0000003 1 WPELL-0000003 4 WPELL-0000004 WPELL-0000004 1 WPELL-0000004 WPELL-0000005 5 WPELL-0000004 WPELL-0000005 2 6 WPELL-0000006 WPELL-0000006 1 WPELL-0000006 7 WPELL-0000007 WPELL-0000007 1 WPELL-0000007 8 WPELL-0000008 WPELL-0000008 1 WPELL-0000008 9 WPELL-0000009 WPELL-0000009 1 WPELL-0000009 10 WPELL-0000010 WPELL-0000010 1 WPELL-0000010 11 WPELL-0000011 WPELL-0000011 1 WPELL-0000011 12 WPELL-0000012 WPELL-0000012 1 WPELL-0000012 13 WPELL-0000013 WPELL-0000013 1 WPELL-0000013 WPELL-0000014 WPELL-0000015 WPELL-0000016 WPELL-0000017 14 WPELL-0000013 WPELL-0000014 2 15 WPELL-0000013 WPELL-0000015 3 16 WPELL-0000013 WPELL-0000016 4 17 WPELL-0000013 WPELL-0000017 5 18 WPELL-0000018 WPELL-0000018 1 WPELL-0000018 19 WPELL-0000019 WPELL-0000019 1 WPELL-0000019 WPELL-0000020 WPELL-0000021 20 WPELL-0000019 WPELL-0000020 2 21 WPELL-0000019 WPELL-0000021 3 22 WPELL-0000022 WPELL-0000022 1 WPELL-0000022 WPELL-0000023 23 WPELL-0000022 WPELL-0000023 2 24 WPELL-0000024 WPELL-0000024 1 WPELL-0000024 25 WPELL-0000025 WPELL-0000025 1 WPELL-0000025 I hope this makes sense. I don't care if the procedure updates the blanks, I am goint to ignore them anyway. I've tried a few cursors and temp tables and I'm not even close.
I appreciate any effort on this one!
-
Here is some code that should work for you, I don't your table name so I just called it Pages:
declare @id int, @Document varchar(15), @lastDocument varchar(15), @page varchar(1000), @lastID int
declare cursPage cursor for
select autoID, Document, Page from pages for read only
set @lastDocument = ''
set @lastID = 0
open cursPage
fetch next from cursPage into @id, @document, @page
while @@fetch_status = 0
begin
if @lastDocument = @document
begin
update pages set PagesInDoc = isnull(PagesInDoc,'') + ' ' + @Page where autoID = @lastID
end
else
begin
set @lastDocument = @document
set @lastID = @ID
update pages set PagesInDoc = Page where autoID = @ID
end
fetch next from cursPage into @id, @document, @page
end
close cursPage
deallocate cursPage
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Kathi,
Worked perfectly!!!
Thank you, thank you, thank you!
Rich
-
I thought this was one of those times that a cursor was a perfect fit, but as I was falling asleep I realized the correct answer.
declare @count int
set @count = 2
update pages set PagesInDoc = Page where PageNum = 1
while exists(select * from pages where PageNum = @count)
begin
update a
set PagesInDoc = a.PagesInDoc + ' ' + b.Page
from Pages a join Pages b
on a.Document = b.Document
where a.PageNum = 1 and b.PageNum = @count
set @count = @count + 1
end
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
One silly question thou is why? Why do you basically want to add data that is denormalized from what the data and tack it into the current table adding extra storage requirements. You should consider only for output and there you could use a user defined function to do this instead in your reports or pivoted data. Also consider the upkeep should the data change, you have to write a trigger to ensure the consistancy of the data, a user defined function can handle it on per-process of requesting the data instead. Just a consideration I wanted to throw in.
-
Kathi,
Thank you again for looking at this. The new process seemed to work, but it wouldn't stop running. After 6 minutes, I killed the process and looked at the PagesInDoc field and it was updating properly, but I didn't check to see if all were there. The cursor version only took 45 sec for over 12,000 records. So, not sure why the new script didn't work, I hope you didn't lose any sleep over this one...
*** One other note, I noticed that the some of the updates in PagesInDoc field further down, which had 30 pages, were truncated. I have the field set for varchar(8000). There should be plenty of room. Any thoughts ...
WPELL-0012075.TIF WPELL-0012076.TIF WPELL-0012077.TIF WPELL-0012078.TIF WPELL-0012079.TIF WPELL-0012080.TIF WPELL-0012081.TIF WPELL-0012082.TIF WPELL-0012083.TIF WPELL-0012084.TIF WPELL-0012085.TIF WPELL-0012086.TIF WPELL-0012087.TIF WPELL-0012088.TIF WPEL
Antares686 ... I totally agree with you, however this is a special little script process that I'm running using xp_cmdshell. The table is only temporary and will be deleted one run. Thank you for your thoughts though...
Rich
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply