Copying an ntext cell from one cell into another (destination row already exists)

• woast

  Valued Member

  Points: 57

  More actions

  May 6, 2004 at 6:13 am

  #107659

  Hi!

  What I'd like to do is:

  UPDATE table1

  SET

  A_TEXT_COLUMN = (SELECT another_text_column

  FROM table2

  WHERE table2_id = @precomputed_id_1)

  WHERE table1_ID = @precomputed_id_2

  Since the cells are text, this does not work. Since the cell to be updated

  is in an already exitant row, it's not possible to simply use insert.

  I'd like to do something like (PSEUDOcode):

  WRITETEXT(table1.A_TEXT_COLUMN,

  READTEXT(@textptr_initialised_to_point_at_target_cell))

  But the *actual* synatx of WRITETEXT and READTEXT seem totally inappropriate

  for any such trick...

  Any hints or pointers HUGELY appreciated... THANX

• woast

  Valued Member

  Points: 57

  More actions

  May 7, 2004 at 1:57 am

  #505607

  Is this a standard *problem*? Or an unsolved one (just an sqlserver limitation that must be dealt with in a totally different way??)

  I saw 10 people read the thread but nobody answered, maybe the problem is a generally troublesome one with no good solutions...

  Any ideas at all?

  Thanx,

  wOAst

• Jonathan Tomkins

  Ten Centuries

  Points: 1248

  More actions

  May 7, 2004 at 4:13 am

  #505627

  Is this what you want? 

  declare @source_ptr binary(16), @target_ptr binary(16), @id1 int, @id2 int

  select

  @source_ptr = textptr(col1) from tbl1 where id = @id1

  select @target_ptr = textptr(col2) from tbl2 where id = @id2

  update

  tbl2 set col2 = 'a' where id = @id2 --to ensure there's something there

  updatetext

  tbl2.col2 @target_ptr 0 null tbl1.col1 @source_ptr

   

   

• woast

  Valued Member

  Points: 57

  More actions

  May 7, 2004 at 5:32 am

  #505637

  excellent!

  thanx!