Geek Answers Handbook

SQL replaces old values ​​with new ones

I have a table called tbl.Products that has a column called articlenumber and is populated with numbers like s401 , s402 , etc.

I created a list with new article numbers that will replace the old ones:

 s401 I00010 s402 I00020 s403 I00030 s403 I00040 ...

I have a request from which I was hoping this would work, but for some reason does nothing.

(of course, I put other values ​​in the request)

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

How to get a query that replaces old values ​​with new ones in a column like this?

+4
source share
2 answers

You just select the newly replaced values ​​and do nothing with them ... it's a very good idea when replacing, always select first to double check that you will get the expected result :)

Update Code -

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

Performing a replacement may have problems (what if you have articles s401 and s4010?), So it will be more reliable to do this as follows:

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

If you have a number of changes, you can create a small script (for example, using Excel, as TheButcher suggested), or you can import data into the tblVals table with the oldVal and newVal columns and then use this statement:

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

This allows you to perform an update in one of the statements, which again will be more reliable than running a script, which may run into problems if it is really long.

A third idea would be to make the logic of constructing a new number for the old number (if such a thing exists) in SQL (or a stored procedure), for example:

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

(Of course, this is completely simplified and probably not enough for your 30k lines).

+10
source

More articles:


All Articles