Sql-Add Number begining of an INT field

[Henry Olive 5]

I wish a healthy day to everyone

 

MyTable : 

DOCNO (it is an Integer Field)

---------

1285

1286

......

 

I want to UPDATE my table and  add shortyear begining of DOCNO.   like below

DOCNO 

---------

211285

211286

......

 

Thank You
 

 

 

 

[baeckerg 6]

How about

Update mytable set docno = docno + 210000;

this modifies all records. If you want to limit the affected rows you would need to add a WHERE clause

Edited July 31, 2021 by baeckerg
more info

[julkas 12]

UPDATE mytable SET DOCNO = CAST(CONCAT('21', DOCNO) AS INT)

 

[Guest]

5 hours ago, baeckerg said:

Update mytable set docno = docno + 210000;

 

1 hour ago, julkas said:

UPDATE mytable SET DOCNO = CAST(CONCAT('21', DOCNO) AS INT)

Both solution are not complete as they will fail after the new year of 22.

 

@Henry Olive You didn't mention what DB server you are using as there is no one unified date functions for SQL across SQL DB providers.

For MS SQL Server you can use YEAR(GETDATE())

for MySql this will work YEAR(CURDATE())

for SQLite it does need to be formatted, please refer to this detailed page https://www.sqlite.org/lang_datefunc.html

...

 

after getting the year, either calculate the mod of it with 100 or take the last two digits (chars).

[Henry Olive 5]

Thank you everybody.

I'm so sorry i forgot to mention Db Server,

It is INTERBASE
 

[baeckerg 6]

My solution was based on the fact that it is an integer field - therefore a simple "ADD" seamed to be the most straightforward way.

I am not working on interbase. Therefore my SQL is based on SQlite. There you could use

UPDATE mytable SET docno = CASE WHEN docno <2000 THEN docno + 210000 ELSE docno + 220000 END;

which would give you more flexibility. In SQLite you could also do 

UPDATE mytable SET docno = CASE WHEN docno <2000 then '21'|| DOCNO ELSE docno + 220000 END;

since SQLite internally handles the fields as strings. But case can give you flexibility in case  you need it

Edited July 31, 2021 by baeckerg
Caps for keywords