ansaurus

Question

Answer 1

+4 A:

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 2009-10-27 07:25:47

great answer could maybe tell me how I do this multiple times?

elhombre 2009-10-27 07:34:49

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.ProductsSet articlenumber = replace(articlenumber, '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 2009-10-27 07:41:13

The_Butcher 2009-10-27 07:42:34

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 2009-10-27 07:57:10

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 2009-10-27 08:25:44

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 2009-10-27 08:30:57

Sorry for answering so late. I assume that with an example you mean an example query. Here you areUpdate 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 2009-10-27 10:51:52

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 2009-10-27 11:17:01

You were totally right, it's a horrible data issue. I had to solve it the manual way but the query worked

elhombre 2009-11-11 09:19:47

Answer 2

+3 A:

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

IronGoofy 2009-10-27 08:12:38

"Update tblProductsSET articlenumber = 'I000010'Where articlenumber = 's401';" = godsent! :D when working with 30k rows!!

The_Butcher 2009-10-27 08:29:31

ansaurus

tags:

views:

answers:

SQL replace old values trough new ones

related questions