Can't get this SQL query to work in update

• //Ravi

  SSCrazy

  Points: 2612

  More actions

  January 30, 2008 at 8:16 am

  #186236

  Hi,

  All I am trying to do is delete zero '0' from the field on the left side if they exist. I am tried these two queries, but both of them are giving me errors.

  UPDATE ASCEND_FC_INSDESC

  SET LEFT (FC_CODE, 1) = ''

  WHERE LEFT (FC_CODE, 1) = '0'

  This give me erroe "Incorrect syntax near the keyword 'LEFT'"

  UPDATE ASCEND_FC_INSDESC

  SET STUFF(FC_CODE, 1, 1, '')

  WHERE LEFT (FC_CODE, 1) = '0'

  Incorrect syntax near ','.

  Both of these select statements are working fine:

  SELECT LEFT (FC_CODE, 1)

  WHERE LEFT (FC_CODE, 1) = '0'

  FROM ASCEND_FC_INSDESC

  and

  SELECT STUFF(FC_CODE, 1, 1, '')

  FROM ASCEND_FC_INSDESC

  Any ideas what I am doing wrong?

  ------------
  🙂

• Andras Belokosztolszki

  SSC-Insane

  Points: 22031

  More actions

  January 30, 2008 at 8:24 am

  #773329

  Hi Rav,

  you were not specifying what should get the new value (just an expression that evaluates to the new value)

  UPDATE ASCEND_FC_INSDESC

  SET FC_CODE=STUFF(FC_CODE, 1, 1, '')

  WHERE LEFT (FC_CODE, 1) = '0'

  Regards,

  Andras

  ---

  Andras Belokosztolszki, MCPD, PhD
  GoldenGate Software

• //Ravi

  SSCrazy

  Points: 2612

  More actions

  January 30, 2008 at 9:46 am

  #773395

  haha. Thanks. Now I feel like a dumbhead. it's like DUH.... I've done these queries before... i guess I haven't had my coffee today..

  Thanks.

  Rav.

  ------------
  🙂