Clever Geek Handbook

Update SQL value

I have a field name which has values ​​C12345, C23456, etc. I need to update the citation field so that all quotes now have 0 after C.

Update account
set citation = 
where citation like 'C%'
+3


source to share


3 answers


Replace should work for this:

Update account
set citation = replace(citation, 'C', 'C0')
where citation like 'C%'

I used sql server instead of the syntax here. Other platforms may vary.



As @sjagr pointed out in the comments

To be sure of the values ​​already set to C0, perhaps it should be where citation not like 'C0%' AND citation like 'C%'

+5


source


Try the following.



Update account
set citation = 'C0' + substring(citation, 2, len(citation))
where citation like 'C%'
+4


source


If you want to repeat a statement, you will need to prevent the modified lines from being candidates the next time you run the same statement:

update account
   set citation = 'C0' + substring(citation, 2, len(citation))
 where citation like 'C%' 
   and citation NOT like 'C0%';
0


source






More articles:


All Articles