-3

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')
rollback

When running this I get

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

closed as off-topic by LowlyDBA, Erik Darling, Aaron Bertrand Feb 21 at 15:42

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – LowlyDBA, Erik Darling, Aaron Bertrand
If this question can be reworded to fit the rules in the help center, please edit the question.

2

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')
rollback

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.

  • 2
    I think newguyin3 intended the replace part for the SET not as a separate SELECT – a_horse_with_no_name Feb 21 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 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 at 16:36
2

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,
                          'ACC_STATUS:Deactivated',
                          'ACC_STATUS:Activated')
    WHERE Name = 'Service1' ;
rollback ;
1

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'

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