SQL replace old values with new ones

问题:

I have a table named tbl.Products, which has a column named articlenumber and is full of numbers like s401, s402, etc.

I generated a list with new article numbers which will replace the old ones:

s401  I00010  
s402  I00020  
s403  I00030  
s403  I00040  
...  

I have a query from which I hoped that it would work out, but somehow it does nothing.

(of course I have nested the other values into the query)

SELECT REPLACE('articlenumber','s401','I00010') FROM tbl.Products

How do I get a query which replaces old values with new ones in a column like this?

回答1:

You are just selecting the newly replaced values and not doing anything with them... thats a very good idea when using replace, always select first to double check that you will get the expected result :)

The update code -

Update  tbl.Products
Set articlenumber = replace(articlenumber, 's401', 'I00010') 

回答2:

Doing a replace can have problems (what if you have an articles s401 and s4010?), therefore it'll be more robust to do it like this:

Update tblProducts
SET articlenumber = 'I000010'
Where articlenumber = 's401';

If you have a number of changes to do, you can either generate a little script (for example with Excel, as TheButcher suggested), or you could import the data into a little table tblVals with the columns oldVal and newVal and then use this statement:

Update tblProducts p
SET articlenumber = (Select newVal
    From tblVals
    where oldVal = p.articlenumber);

This allows you to do the update in one statement which again will be more robust than running a script which may run into problems if it is really long.

A third idea would be to do the logic in constructing the new number for the old number (if such a thing exists) in SQL (or a Stored Procedure) like this:

Update tblProducts p
SET articlenumber = 'I0000' || Right(articlenumber,1) || '0'

(Of course this is totally simplified and probably not nearly sufficient for your 30k rows).