I tried googling this but every post I find, the OP is using insert or join. Well I am not. I am trying to replace a string in a column, Details where Name = Service1

begin tran
UPDATE [User] 
SET Details WHERE Name=Service1 SELECT replace('ACC_STATUS:Deactivated', 'Deactivated',  'Activated')

When running this I get

Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'WHERE'.


Assuming 'Service1' is a string, here is a rewritten version, using 'Something' as what you're trying to update Details to:

begin tran
UPDATE [User] 
SET Details = 'Something'
WHERE Name= 'Service1';
SELECT replace('ACC_STATUS:Deactivated', 'Deactivated',  'Activated')

Note that I've also added a semicolon after the update statement - when you start something new, like a separate SELECT query, that's a separate statement. I would separate those with a semicolon and put them on a new line just to be safe and make it easy for readers later.

If Service1 isn't a string - if it's a reference to some other object or a query result - then you'll need to explain more about what you're trying to accomplish.

| improve this answer | |
  • 2
    I think newguyin3 intended the replace part for the SET not as a separate SELECT – a_horse_with_no_name Feb 21 '19 at 14:25
  • 1
    @a_horse_with_no_name wow, you're better than me! I couldn't figure out what on earth he was aiming for there. – Brent Ozar Feb 21 '19 at 14:59
  • @BrentOzar You are SQL Guru and at the same time, your sense of humor is away from earth(fantastic), Hats Off!! I am sorry, this comment is off the topic; but, couldn't resist myself from commenting on your statement. – Learning_DBAdmin Feb 21 '19 at 16:36

The syntax of a (simple) UPDATE is:

UPDATE table_name
SET column_name = <expression_that_will_update_the_column>
WHERE <some_filters_that_decide_which_rows_to_be_updated>

If you want (it's not very clear, please clarify) to update Details and replace 'Deactivated' with 'Activated' when the Details contains the substring 'ACC_STATUS:Deactivated', then the following should do.

It's also not clear whether Service1 is a column name or 'Service1' a string to be searched in Name and the filter to be applied (I assumed the 2nd):

begin tran
    UPDATE [User] 
    SET Details = replace(Details,
    WHERE Name = 'Service1' ;
rollback ;
| improve this answer | |

Unless I'm missing something, I think you want the "SELECT" piece in the same line as the "SET".

UPDATE [User] 
SET Details = replace(Details, 'Deactivated',  'Activated')
WHERE Name='Service1'
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.