6

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?

TylerH
  • 20,799
  • 66
  • 75
  • 101
elhombre
  • 2,839
  • 7
  • 28
  • 28

2 Answers2

17

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).

Thorsten
  • 12,921
  • 17
  • 60
  • 79
  • "Update tblProducts SET articlenumber = 'I000010' Where articlenumber = 's401';" = godsent! :D when working with 30k rows!! – The_Butcher Oct 27 '09 at 08:29
10

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')
The_Butcher
  • 2,440
  • 2
  • 27
  • 38
  • great answer could maybe tell me how I do this multiple times? – elhombre Oct 27 '09 at 07:34
  • 2
    I think the best way is to do it in excel. Have the old values in column A and the new values in column B. Then in column C write your query with placeholders for the values - Update tbl.Products Set articlenumber = replace(articlenumber, '&A1&', '&B1'); Then just drag that little black tooltip on the bottom right hand corner of the cell all the way to the bottom of your spreadsheet and viola, it will generate all your scripts. Hope it helps – The_Butcher Oct 27 '09 at 07:41
  • 1
    i forgot the "&" after the B1 :D – The_Butcher Oct 27 '09 at 07:42
  • Thank you very much for being so fast, I just made that and then copied the query into Microsoft SQL Server Management Studio Express. But when I execute the query I just get a message that 30000 rows are affected then I get no view. When I go back into the table view nothing has changed. What am I doing wrong? – elhombre Oct 27 '09 at 07:57
  • 30 000 rows affected will still show to tell you that the query was run against 30 000 rows. If nothing changed this means that the data you are trying to update does not match your statement. Make sure the statements is UPDATE table SET column = replace(column,'OLDVALUE','NEWVALUE'); have you got a sample for me? – The_Butcher Oct 27 '09 at 08:25
  • Lastly make sure that the column name is NOT in single quotes('') as the script will still run showing 30 000 rows affected – The_Butcher Oct 27 '09 at 08:30
  • Sorry for answering so late. I assume that with an example you mean an example query. Here you are Update  tbl.Products Set articlenumber = replace(articlenumber, 'S401', 'I00010'); Update  tbl.Products Set articlenumber = replace(articlenumber, 'S402', 'I00020'); Update  tbl.Products Set articlenumber = replace(articlenumber, 'S403', 'I00030'); Update  tbl.Products Set articlenumber = replace(articlenumber, 'S404', 'I00040'); – elhombre Oct 27 '09 at 10:51
  • the query is correct so it must be a data issue.. check if the correct table is being used and check the case of the string "S" versus "s" – The_Butcher Oct 27 '09 at 11:17
  • You were totally right, it's a horrible data issue. I had to solve it the manual way but the query worked – elhombre Nov 11 '09 at 09:19