Update Query Assistance

Johnathan Simpson 586 Reputation points
2022-05-05T17:22:26.543+00:00

Should be a basic update query, but vfr is never updated. What is incorrect in my queyr?

Update Mosa
  Set pv = 0
  ,vfr = 'Invalid Input;'+vfr
  WHERE Quantity = 0 OR Quantity IS NULL
  AND processed_file IS NULL
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

Accepted answer
  1. Naomi 7,366 Reputation points
    2022-05-05T17:36:48.01+00:00

    In this case, it is a simple fix:

    Update Mosa
      Set pv = 0
      ,vfr = 'Invalid Input;'+ISNULL(vfr,'')
      WHERE Quantity = 0 OR Quantity IS NULL
      AND processed_file IS NULL
    

    Run it only once.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,721 Reputation points
    2022-05-05T19:19:39.43+00:00

    Just to add to Naomi's response.

    CONCAT is a safer way to combine strings. It handles NULL values.

    vfr = CONCAT('Invalid Input;',vfr)
    
    1 person found this answer helpful.
    0 comments No comments

  2. Naomi 7,366 Reputation points
    2022-05-05T17:26:11.12+00:00

    The query looks OK. If you turn it into select statement, do you get result? Also, you can add OUTPUT Deleted., Inserted. to see what happened.