How to add 10000 to existing column?

Question

I have a table called stock and one of the column is called stock_code. The stock_code is all 5 digit int (I believe), but now I'm running out of numbers, I need to change all the code to 10 digit. So I need to add 10000 to all the stock_code. Can someone help me please?

Please add sample input and output, also the query you hv tried so far. — Abdul Rasheed, Feb 12 '18 at 05:37
What is the type of the `stock_code` column, and which database are you using? — Tim Biegeleisen, Feb 12 '18 at 05:37
For example: Apple Product code 20706 will become Apple Product Code 1000020706 — Howard Kuo, Feb 12 '18 at 05:41
you are still not mentioned the datatype of `stock_code` and the `RDBMS` you are using.. — Abdul Rasheed, Feb 12 '18 at 05:43
That's not *adding* 10000 to STOCK_CODE, but *concatenating* 10000 with STOCK_CODE. That would be an `UPDATE` statement. — Littlefoot, Feb 12 '18 at 06:12
Why not simply continue with 6 digit numbers? If your current highest stock_code is 99999 then just continue with 100000. I don't see any reason why you would need to change _existing_ values for that. — , Feb 12 '18 at 06:55

score 1 · Accepted Answer · answered Feb 12 '18 at 06:45

1

If it's a number:

UPDATE stock SET stock_code = 1000000000 + stock_code;

or, to keep a better view on the number of zeros:

UPDATE stock SET stock_code = (10000 * 10000) + stock_code;

answered Feb 12 '18 at 06:45

user unknown

35,537
11
75
121

score 0 · Answer 2 · edited Feb 12 '18 at 06:53

0

For Oracle:

update your_table set stock_code=concat(10000,stock_code)

For NexusDB

 update your_table SET stock_code = '10000' + stock_code

edited Feb 12 '18 at 06:53

answered Feb 12 '18 at 06:11

venkatesh

151
13

I tried this before, but got this error message: NexusDB: Query: Query execution failed; Unexpected exception object raised: [exception] unknow function:concat at line 1, pos 31 – Howard Kuo Feb 12 '18 at 06:30
use concat operator using + operator we can use it in nexusdb i will edit my answer with that wait ... – venkatesh Feb 12 '18 at 06:43

Abdul Rasheed · Answer 3 · 2018-02-12T06:45:24.103

0

Try this, (hope your stock_code column is string)

UPDATE stock SET stock_code = '10000' + stock_code

this will concatenate 10000 to your existing column stock_code.

If the stock_code column is numeric, then you can simply use the below query

UPDATE stock SET stock_code = 1000000000 + stock_code

edited Feb 12 '18 at 06:45

answered Feb 12 '18 at 06:40

Abdul Rasheed

6,486
4
32
48

How to add 10000 to existing column?

3 Answers3