Update on a One-to-Many

• UH

  Newbie

  Points: 1

  More actions

  August 25, 2006 at 12:42 pm

  #169998

  I need to do an update on a table pulling data in from another table.

  Here is what Table1 looks like

  FieldId Text

  --------------

  1 ABCD

  Here is what Table2 looks like

  FieldID Text

  ---------------

  1 efgh

  1 xyz

  I need to pull Text from Table2 and append it to text in Table1

  The problem is, when I do the Update (given below), it only appends one of the two entries from Table 2. So the end result is

  Table1

  FieldId Text

  ------------------

  1 ABCDefgh

  What I want it to look like is

  FieldId Text

  ---------------------

  1 ABCDefghxyz

  Here is the query I am running

  update table1

  set table1.text = (convert(varchar(1000), table1.text)

  + convert(varchar(1000), table2.text))

  from table1 , table2

  where table1.fieldid = table2.fieldid

  Thanks in advance.

• JKSQL

  SSCrazy Eights

  Points: 8481

  More actions

  August 25, 2006 at 2:20 pm

  #656935

  The only way I have ever gotten a statement like that to work is by using a cursor or while.

• jason-154083

  SSC Rookie

  Points: 26

  More actions

  August 28, 2006 at 7:19 am

  #657045

  ALL CODE SHOWN HAS NOT BEEN TESTED.

  THE EXAMPLE BELOW IS INTENDED A GUIDE ONLY.

  DO NOT RUN ANYTHING ON A LIVE SYSTEM WITHOUT THOROUGHLY TESTING FIRST.....REALLY.

  I basically made everything up here on the fly. Like I said it is designed to get you thinking of a path you could follow.

  hope it helps.

  If you find a good answer to the problem could you post it so I could have a look.

  Thanks.

  CREATE FUNCTION dbo.ConcatRows (@id int)

  RETURNS varchar(1000)

  AS

  BEGIN

  --Make sure the variable is big enough by checking with

  --something like...

  /*

  select max(SumOfText) as [variable_length]

  from (select sum(len(Text)) as SumOfText

  from table_2

  group by FieldId) as temp;

  */

  --This should give you the correct size for the variable below.

  DECLARE @row varchar(????);

  --Concatenate each field together to make one large row.

  select @row = COALESCE(@row, '') + [Text] from table_2 where FieldId = @id;

  RETURN (rtrim(@row));

  END;

  --This is how the update would go...

  --id in ConcatRows(id) is the id in table one that is being updated.

  --Therefore for id(1) the function returns as a string all the Text

  --fields that correspond to a FieldId of 1.

  --This is then concatenated onto the existing value in table_1.

  update table_1 set [Text] = [Text] + dbo.ConcatRows(FieldId);