Update column – remove first and last characters

I have written a successful SELECT statement to both show me all the company names that are surrounded by double quotes like “ABC Company” for example. I want to permanently remove the double quotes and replace the company name with the cleaned version.

Here is my SELECT that works great:

SELECT
    companyname,
    CASE
       WHEN companyname LIKE '"%"' 
          THEN SUBSTRING(companyname, 2, LEN(companyname) - 2)
       ELSE companyname
    END AS CC
FROM 
    Company_Table 
WHERE 
    companyName LIKE '"%"'

I am having trouble converting this statement to an UPDATE. I want to affect the original companyname column in place.

I have tried to add UPDATE clauses around the above code, but I just cannot seem to get it right.

Can I please get an example of how I can remove the first and last chars of a column in place.

Otherwise, it is manual labor!

I expect that the original companyname field will be devoid of “”.

Answer

As an update:

UPDATE Company_Table 
SET companyname = SUBSTRING(companyname, 2, LEN(companyname)-2)
WHERE companyName like '"%"'

Single table update queries take the form

UPDATE table
SET column1 = value1, column2 = value2 ...
WHERE truth_test

You can use the same column names on the left and the right of the = in the SET. All the right hand sides are worked out then applied to the lefts so the values don’t interfere with each other

(if you were to say SET a = a + 1, b = a + 1 then for a row where both a and b started out as 0, they would both be 1 after the update. The a doesn’t increment from 0 to 1 then the b become 1+1. In other words, b is set to old-a + 1 not new-a + 1