Help in using Update/Set and variables

• pauten

  SSC Rookie

  Points: 43

  More actions

  August 13, 2008 at 8:29 am

  #190940

  I have the following code:

  /* CustPmt - Copy the Payment Number into the Comment Fiefd */

  declare @oldcomment varchar(50);

  declare @newcomment varchar(50);

  declare @trancmt varchar(50);

  declare @custpmtno varchar(50);

  begin

  update stgCustpmt

  set @oldcomment = TranCmt;

  set@custpmtno = cast(CustPmtNo as varchar);

  set@newcomment = 'OldPmtNo: '+cast(CustPmtNo as varchar)+' Cmt: '+@oldcomment;

  setTranCmt = @newcomment;

  end

  I want to go thru each row, and copy the pmntno field into a format and then put it into the trancmt field.

  The problem that I am running into is it doesn't like the set that actuatlly updates the column.

  Is there another way I can/should be doing this?

  Thanks,

  Phillip

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  August 13, 2008 at 8:42 am

  #857481

  This should do what you want:

  [font="Courier New"]UPDATE stgCustpmt

     SET TranCmt = 'OldPmtNo: ' + CAST(CustPmtNo AS VARCHAR(20)) + ' Cmt: ' + TranCmt[/font]

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• pauten

  SSC Rookie

  Points: 43

  More actions

  August 13, 2008 at 9:06 am

  #857509

  Jack,

  You are quickly becoming my hero. 😀

  However, when I try it that way, all the comment are blank. I can do just the TranCmnt = CustPmtNo and that works, but building it up doesn't. 🙁

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  August 13, 2008 at 9:30 am

  #857521

  When you say blank I am going to assume you mean NULL as Concatenating to a NULL yields NULL. So if there is a possibility that one or both of your columns in the concatenation could contain NULLS you need to use the ISNULL() function (IsNull(TranCmt, '')).

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• pauten

  SSC Rookie

  Points: 43

  More actions

  August 13, 2008 at 10:04 am

  #857544

  That helped, I had to throw another cast in there to truncate the entire value, but isnull did the trick.

  Thanks,

  Phillip