CODEWITHSUNDEEP
mysql
George A. Custer
George A. Custer

Reputation: 121

Starting from unique and progressive `ID` to build a new string using MySQL

Starting from a MySQL table with the following unique and progressive IDs

ID pID
1
10
100
1000

I need update the column pID of the same MySQL table by constructing a string of type YYYY-ID

Expected Output:

ID pID
1 2022-0001
10 2022-0010
100 2022-0100
1000 2022-1000

Any help really appreciated.

Upvotes: 1

Views: 50

Answers (1)

Valeriu Ciuca
Valeriu Ciuca

Reputation: 2094

You can CONCAT '2022-' string to your ID column, padded with LPAD, filled with 0 on the left:

UPDATE table SET pID = CONCAT('2022-', LPAD(ID, 4, 0))

If you need current year:

UPDATE table SET pID = CONCAT(DATE_FORMAT(CURDATE(), '%Y'), '-', LPAD(ID, 4, 0))

Upvotes: 4

Related Questions