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.
CodePudding user response：
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))