locked
SQL Update Query Syntax Error - MS Access RRS feed

  • Question

  • Hello,

    I'm trying to write a query which updates one column within a table if a specific code is included in the cell in another column. The data is within the same Access database table. If a row in the "Comments" column begins with "(!0!) -" I want to enter the text "AssocR" in the "Note" column.

    I've spent the last hour looking online for code, the below script seems straightforward, but I am getting a Syntax error It's highlighting the column name "Note". I've tried it with and without parenthesis. Any idea where my mistake is?

    update CSU_Approval_OriginalDetail 
    set Note ='AssocR'
    where Comments Like ("(!0!) -*");


    Lorac1969


    • Edited by Lorac1969 Wednesday, June 21, 2017 7:52 PM
    Wednesday, June 21, 2017 7:23 PM

Answers

All replies

  • In the sql world we would write this like this:

    update CSU_Approval_OriginalDetail 
    set Note ='AssocR'
    where Comments Like '(!0!) -%';

    Does this work?

    Wednesday, June 21, 2017 8:23 PM
  • You used MS Access wildcards, they don't work in Transact-SQL / SQL Server.

    See LIKE (Transact-SQL) and Pattern Matching in Search Conditions


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, June 22, 2017 6:41 AM
  • Hi Hilary, no unfortunately I still get the Syntax error on "Note" after making the change you suggested, but thanks for pointing out the fact I was using the wrong symbol as a wildcard :)

    Lorac1969

    Friday, June 23, 2017 6:14 PM
  • Thanks for the tip, I saw from Hilarys example it should be the % symbol.

    Lorac1969

    Friday, June 23, 2017 6:14 PM
  • Did you also change the LIKE literal enclosure from double quotes to single quotes as in Hilary's example?

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, June 23, 2017 6:21 PM
    Answerer
  • No I missed that the first time, it works now!! Many thanks to Hilary Cotter and Dan Guzman!!

    Lorac1969

    Monday, June 26, 2017 2:35 PM