SQL update column twice

Question

I have a string column (Col1) in table (Tab1). I want to write a script that removes '%' or '&' characters from the string values.

The select for this is the following:

SELECT REPLACE( Tab1.Col1, '&', ''), 
       REPLACE(Tab1.Col1, '%', '')
FROM Table1 Tab1
WHERE Tab1.Col1 like '%[&]%' 
      OR Tab1.Col1 like '%[%]%'

Does this seem correct? If it is how would I convert this to any update statement? I tried the following:

UPDATE Tab1
SET Tab1.Col1 =  REPLACE( Tab1.Col1, '&', ''),  
    Tab1.Col1 = REPLACE (Tab1.Col1, '%', '')
FROM Table1 Tab1
WHERE Tab1.Col1 like '%[&]%' 
      OR Tab1.Col1 like '%[%]%'

This doesn't work as you cant update column twice in SET. Is there any other way I can do this? I know I'm probably doing something silly here so apologies for my ignorance.

Thanks in advance.

score 5 · Accepted Answer · answered Dec 14 '10 at 10:00

This should work. Replace returns a string which you can again pass to another replace function.

UPDATE Tab1 
    SET Tab1.Col1 =  REPLACE(REPLACE( Tab1.Col1, '&', ''), '%', '') 
    FROM Table1 Tab1 
    WHERE Tab1.Col1 like '%[&]%' OR Tab1.Col1 like '%[%]%'

score 1 · Answer 2 · answered Dec 14 '10 at 10:00

1

 UPDATE Tab1
 SET Tab1.Col1 =  REPLACE(REPLACE( Tab1.Col1, '&', ''), '%', '') 
 FROM Table1 Tab1
 WHERE Tab1.Col1 like '%[&]%' 
  OR Tab1.Col1 like '%[%]%'

answered Dec 14 '10 at 10:00

Michael Pakhantsov

24,855
6
60
59

score 0 · Answer 3 · 2010-12-14T13:11:13.077

0

As you don't say what RDBMS you are using, I'm avoiding things that are obviously non-portable:

UPDATE Tab1 
SET Tab1.Col1 = REPLACE(REPLACE(Tab1.Col1, '&', ''), '%', '') 
WHERE Tab1.Col1 <> REPLACE(REPLACE(Tab1.Col1, '&', ''), '%', '');

edited Dec 14 '10 at 13:11

answered Dec 14 '10 at 10:44

"I'm avoiding things that are obviously non-portable" -- erm, Standard SQL uses `<>` rather than `!=`. – onedaywhen Dec 14 '10 at 12:09
@onedaywhen 'SQL Standard'<>'Portable' but you are [right](http://stackoverflow.com/questions/723195/should-i-use-or-for-not-equal-in-tsql/723426#723426) :) – Dec 14 '10 at 13:13

SQL update column twice

3 Answers3