Writetext to all rows returned

• Jones'e

  Right there with Babe

  Points: 744

  More actions

  July 7, 2009 at 9:59 am

  #73932

  Need help figuring out the best way to use writetext to update all rows returned in my query below.

  This query only updates the last result.

  Any ideas would be helpful.

  DECLARE @oper char(4)

  Set @oper = 'tpr1'

  Declare @ptrval binary(16)

  Select @ptrval = textptr(inrtgs.fopermemo)--inmast.fcstscode, *

  from inrtgs

  join inmast on inrtgs.fpartno = inmast.fpartno and inrtgs.fcpartrev = inmast.frev

  where fcstddesc = @oper and inmast.fcstscode <> 'O' and inrtgs.fpartno = 'TEST1021-2'

  Writetext inrtgs.fopermemo @ptrval 'test2'

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  July 19, 2009 at 11:11 pm

  #1026736

  Thought I'd "bump" this for you... do you still need help on this?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
  "Change is inevitable... change for the better is not".
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  Intro to Tally Tables and Functions

• Jones'e

  Right there with Babe

  Points: 744

  More actions

  July 20, 2009 at 9:17 am

  #1027050

  Thanks Jeff

  I decided to just put it into a cursor, then update each one from there.

• souLTower

  SSCarpal Tunnel

  Points: 4026

  More actions

  July 22, 2009 at 12:43 pm

  #1028607

  Another solution could be:

  -- Create a table to hold your text value

  create table #txt (txt text)

  insert into #txt (txt) values('myy value here')

  -- This demos how to update the text value

  create table #foo (iid int identity (1, 1), myTXT text)

  -- Insert some dummy data

  insert into #foo (myTXT)

  select null union all

  select null union all

  select null union all

  select null union all

  select null union all

  select null union all

  select null union all

  select null

  -- Update based on a join

  update F set F.myTXT = X.txt from #txt X inner join #foo F on 1 = 1

  -- View the results

  select * from #foo