Update Memo field with many records 1

[mikej336]

I have a table,

ID Phonenums
--- ---------
123 1231231234
123 1231231234
123 1231231234
123 1231231234
123 1231231234

both text fields.

I need to update anouther table with the above data but I need the Phonenums in a single memo field in a single record. (Preferably with line feed between each number so they line up down the field.)

ID Memo_Phonenums
--- ---------
123 1231231234
1231231234
1231231234
1231231234
1231231234
124 2131231234
etc..

I tried a standard update with no success. Any ideas?

Thanks

Mike

 

[kaht]

Here's how to get all the data arranged in one place (in a variable), all seperated by a linefeed:

declare @a varchar(1000)
declare @b table(memo char(10))

insert into @b
select '1231231234' union
select '2132132134' union
select '3123123124'

set @a = ''
select @a = @a + memo + char(10) from @b

select @a

Now, in order to update a whole table for each id I can't envision a set based solution. So, perhaps a cursor is necessary?

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]

 

[mikej336]

Sorry, I did not specify. I need the all the records from the first table to be updated to the second table.

Thanks

Mike

 

[kaht]

I need the all the records from the first table to be updated to the second table

Right. You need all the records from the first (multiple records per id) to be updated to the second table (one record per id). My example illustrated how to pull the multiple records and store them in a single variable (seperated by a newline). From there, you can use a cursor to use this method for each id to compound all the info per id, then update the compounded data into the second table.

I'm not sure if this is the most efficient way or not, but it will work.

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]

 

[PerlyGates]

Hi Mike - are you using 2000 or 2005?

-PG

 

[mikej336]

2005

Mike

 

[PerlyGates]

Hi Mike

The following will work using recursion - however BE WARNED - it will be VERY slow (even the initial population of the CTE - common table expression - not to mention the actual UPDATE ) and its not fully tested. Bare in mind that it's not as complicated as it looks (famous last words) but another approach might be more optimal.

SET NOCOUNT ON;

DECLARE @tv TABLE ( 
   id varchar(10),
   phonenums varchar(30)
)
INSERT INTO @tv 
SELECT '123','1231231234' UNION ALL
SELECT '123','1231231234' UNION ALL
SELECT '123','1231231234' UNION ALL
SELECT '123','1231231234' UNION ALL
SELECT '888','8831231234' UNION ALL
SELECT '999','9931231234' UNION ALL
SELECT '999','9931231234' UNION ALL
SELECT '999','9931231234' UNION ALL
SELECT '999','9931231234'

-- Declare and populate Destination table 

DECLARE @dest_tv TABLE ( 
   dest_id varchar(3),
   dest_phonenums varchar(1000)
)

INSERT INTO @dest_tv (dest_id )
SELECT '123' UNION ALL
SELECT '888' UNION ALL
SELECT '999'; -- [blue] Note use of semi-colon before declaring CTE [/blue]

/* The following will uses 
   a recursive CTE query */
   

[green] -- Declare you're CTE t2 [/green] 
WITH t2( id, myCount,allNumbers, myLength ) 
[green] -- Populate you're CTE t2 [/green]
 AS ( SELECT id 
            ,COUNT(*) OVER (PARTITION BY id)
            ,CAST( phonenums AS VARCHAR(100))
            ,1
       FROM @tv 

      UNION ALL

     SELECT t2.id
           ,t2.myCount 
           ,CAST(t2.allNumbers + CHAR(10) + t1.phonenums AS VARCHAR(100))
            ,t2.myLength + 1 
       FROM @tv t1
    INNER JOIN
			t2
         ON t1.id        = t2.id 
      WHERE t2.myLength  < t2.myCount)
[green]-- Perform UPDATE [/green] 
UPDATE @dest_tv
   SET dest_phonenums  = allNumbers 
 FROM ( SELECT DISTINCT 
               id 
              ,allNumbers 
          FROM t2
         WHERE myLength = myCount ) t3
WHERE dest_id     = t3.id 


SELECT * FROM @dest_tv
SELECT * FROM @tv

 

[mikej336]

Thanks guys, for your help I am learning alot.

But I am still missing a few things.

Let me restate my problem.

I have an existing table A. It has a all phonenumbers associated with different customers by customer ID. One record per phone number. Customers may have any number of numbers.

Select * from A where ID ='123'

might return (as an example)

ID Phonenums
--- ---------
123 1231231231
123 1231231232
123 1231231233
123 1231231234
123 1231231235

I also have an existing table B that a just has customer ID's and an empty memo field which needs to contain all corrisponding phonenumbers for each particular customer. This table feeds an imaging system that will search the memo field for customer lookups.

Select * from B where id = '123'

would return

ID Memo_Phonenums
--- ---------
123

What I need is to populate Memo_Phonenums with the valid phonenums from table A. Once populated...

Select * from B where ID ='123'

might return (as an example)

ID Memo_Phonenums
--- ---------
123 1231231231
1231231232
1231231233
1231231234
1231231235

***************************************************

Played with both your examples and I am still lost.

I figured out the a basic cursor and was able to go thru the table one record at a time. But, I was not able to accumlate them into a variable and update the table B.

PerlyGates, I am still attempting to decifer yours.

Thanks again.

Mike

 

[PerlyGates]

Hi Mike - if you want to post up all field names/table names etc. (or abbreviations/aliases of in the interests of privacy) together with their datatype definitions e.g. char,varchar,int etc I'll plug them into my code for you and re-post.

 

[mikej336]

SELECT actbrch,[SVCID]
FROM [400_Metafile].[dbo].[yad_srvmast]
where actbrch = '0000000001ACCT'

actbrch SVCID
--------------- ----------
0000000001ACCT 1234635036
0000000001ACCT 1234635003
0000000001ACCT 1234632071

[SVCID] [nvarchar](10) COLLATE
[ActBrch] [nvarchar](15) COLLATE

********************************************

SELECT [ActBrch], [PhoneNumbers]
FROM [400_Metafile].[dbo].[BillTest]
where actbrch = '0000000001ACCT'

ActBrch PhoneNumbers
--------------- --------------------------------0000000001ACCT NULL

(1 row(s) affected)

[ActBrch] [varchar](15) COLLATE
[PhoneNumbers] [text] COLLATE

Thanks again PerlyGates

 

[PerlyGates]

Try this ...

WITH t2( ActBrch, myCount,[SVCID], myLength ) 
-- Populate you're CTE t2 
 AS ( SELECT ActBrch 
            ,COUNT(*) OVER (PARTITION BY ActBrch)
            ,CAST( [SVCID] AS VARCHAR(100))
            ,1
       FROM [400_Metafile].[dbo].[yad_srvmast]

      UNION ALL

     SELECT  t2.ActBrch
            ,t2.myCount 
            ,CAST(t2.[SVCID] + CHAR(10) + t1.[SVCID] AS VARCHAR(100))
            ,t2.myLength + 1 
       FROM [400_Metafile].[dbo].[yad_srvmast] t1
    INNER JOIN
            t2
         ON t1.ActBrch     = t2.ActBrch
      WHERE t2.myLength  < t2.myCount)
-- Perform UPDATE 
UPDATE [400_Metafile].[dbo].[BillTest]
   SET [PhoneNumbers]  = t3.[SVCID]
 FROM ( SELECT DISTINCT 
               ActBrch 
              ,[SVCID] 
          FROM t2
         WHERE myLength = myCount ) t3
WHERE [400_Metafile].[dbo].[BillTest].ActBrch     = t3.ActBrch


SELECT * FROM [400_Metafile].[dbo].[BillTest]
SELECT * FROM [400_Metafile].[dbo].[yad_srvmast]

 

[mikej336]

Hey Perly,

I am getting...

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Any ideas?

Thanks

Mike

 

[PerlyGates]

... yeah I was afraid of that - whats the largest number of occurrences of any one ActBrch? How many rows are in the tables (as they seem pretty big - banking data and all that)?

As I outlined at the outset it is quite probable that this query will just be too slow to make it anyway feasible so an alternative solution maybe your best bet.

The default number of recursions is 100 but you can over-ride that so let me know the above i.e. max. number of occurences of a ActBrch and no. of rows in the table.

- PG

 

[mikej336]

There are 10 accounts with over 100 phonenumbers, the highest number being 317.

There are 62549 total phonenumber records.

Thanks again

Mike

 

[kaht]

Here's the cursor example, this should clear up any confusion:

[COLOR=blue]declare[/color] @a [COLOR=blue]table[/color] (foo [COLOR=blue]tinyint[/color], bar [COLOR=blue]char[/color](1))
[COLOR=blue]declare[/color] @b [COLOR=blue]table[/color] (foo [COLOR=blue]tinyint[/color], bar [COLOR=blue]varchar[/color](100))

[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a
[COLOR=blue]select[/color] 1, [COLOR=red]'a'[/color] union
[COLOR=blue]select[/color] 1, [COLOR=red]'b'[/color] union
[COLOR=blue]select[/color] 1, [COLOR=red]'c'[/color] union
[COLOR=blue]select[/color] 2, [COLOR=red]'x'[/color] union
[COLOR=blue]select[/color] 2, [COLOR=red]'y'[/color] union
[COLOR=blue]select[/color] 2, [COLOR=red]'z'[/color]

[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b
[COLOR=blue]select[/color] 1, [COLOR=red]''[/color] union
[COLOR=blue]select[/color] 2, [COLOR=red]''[/color]

[COLOR=blue]select[/color] * [COLOR=blue]from[/color] @a
[COLOR=blue]select[/color] * [COLOR=blue]from[/color] @b

[COLOR=blue]declare[/color] @c [COLOR=blue]tinyint[/color]
[COLOR=blue]declare[/color] @d [COLOR=blue]varchar[/color](100)

[COLOR=blue]declare[/color] cur [COLOR=blue]cursor[/color] [COLOR=blue]for[/color]
[COLOR=blue]select[/color] [COLOR=#FF00FF]distinct[/color] foo [COLOR=blue]from[/color] @a

[COLOR=#FF00FF]open[/color] cur
[COLOR=blue]fetch[/color] [COLOR=blue]next[/color] [COLOR=blue]from[/color] cur [COLOR=blue]into[/color] @c
[COLOR=blue]while[/color] @@fetch_status = 0 [COLOR=blue]begin[/color]
   [COLOR=green]--reset the variable back to an empty string
[/color]   [COLOR=blue]set[/color] @d = [COLOR=red]''[/color]

   [COLOR=blue]select[/color] @d = @d + bar + [COLOR=blue]char[/color](10)
   [COLOR=blue]from[/color] @a
   [COLOR=blue]where[/color] foo = @c

   [COLOR=blue]update[/color] @b
   [COLOR=blue]set[/color] bar = @d
   [COLOR=blue]where[/color] foo = @c

   [COLOR=blue]fetch[/color] [COLOR=blue]next[/color] [COLOR=blue]from[/color] cur [COLOR=blue]into[/color] @c
[COLOR=blue]end[/color]
[COLOR=blue]close[/color] cur
[COLOR=blue]deallocate[/color] cur


[COLOR=blue]select[/color] * [COLOR=blue]from[/color] @b

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]

 

[SQLSister]

It really is best not to store data this way but to use your user interface to show it to the user the way you want it. JUst suggesting that might be a better way to go. You won't have data integrity issues (becasue one table was updated and the other wasn't and you avoid the cursor route to populate the second table. Not sure what you are using this for, but a differnt solution might be the best way to go.

"NOTHING is more important in a database than integrity." ESquared

 

[PerlyGates]

Hi Mike

As kaht has so kindly supplied a ready-made solution I'd go with that (assuming SQLSister's suggestion is a non-runner). Not only is kaht's iterative solution easier to read and understand it will be "blisteringingly" faster than my donkey code. Apologies for leading you down a bit of a blind alley but at least you might have picked up something about recursion.

-PG

 

[mikej336]

SQLSister, I agree but unfornuatly the data feeds an imaging system that uses a single field.

Kaht I will take a look and see if I can get your example working today.

Thanks

Mike

 

[mikej336]

Kaht,

I managed to get it running. I took about an hour and 2 minutes to process 31 k id's.

That seems slower then what I expected but I apprecieate all the help.

Here is what I wound up with....

declare @c varchar(25)
declare @d varchar(2000)
declare @e int

declare cur cursor for
select distinct [Actbrch] from [400_Metafile].[dbo].[BillTest]

select @e=1

open cur
fetch next from cur into @c
while @@fetch_status = 0 begin
--reset the variable back to an empty string
set @d = ''


select @d = @d + [SVCID] + char(10)
from [400_Metafile].[dbo].[yad_srvmast]
where [ACTBRCH] = @c

update [400_Metafile].[dbo].[BillTest]
set [Phonenumbers] = @d
where [ACTBRCH] = @c

fetch next from cur into @c

print @c
select @e=@e + 1
print @e
end
close cur
deallocate cur

select [actbrch], [phonenumbers] from [400_Metafile].[dbo].[BillTest]


Thanks again

Mike