I have an sql field issue_code data type string with the following sample data format "2020-12-0012" with hundreds of records i want to update all from "2020" to "2021" without modifying the rest of the record. how can i query this?
4
-
Are you sure it's "2020-12-0012" with two zeros in last group?Konorlevich– Konorlevich2021-02-24 14:24:52 +00:00Commented Feb 24, 2021 at 14:24
-
Which dbms are you using? When it comes to date/time, many products are far from ANSI SQL compliant.jarlh– jarlh2021-02-24 14:50:22 +00:00Commented Feb 24, 2021 at 14:50
-
sorry the field is not a date but a unique string. i just need the right query on how to do this on mysql.BARMM ICTS– BARMM ICTS2021-02-24 15:00:01 +00:00Commented Feb 24, 2021 at 15:00
-
Found it.. $query = UPDATE table_name SET issue_code = REPLACE(issue_code, '2020', '2021') WHERE issue_code LIKE '%2020%';BARMM ICTS– BARMM ICTS2021-02-24 15:08:07 +00:00Commented Feb 24, 2021 at 15:08
Add a comment
|
1 Answer
If your field is DATE, this could be helpful
If your field is text, you could use this Please, check date format
#example for MySQL
UPDATE Test.test_table
SET
issue_code=DATE_FORMAT(DATE_ADD(STR_TO_DATE(issue_code, '%Y-%m-00%d'), INTERVAL 1 YEAR), '%Y-%m-00%d')
# you may add this condition
# WHERE
# YEAR(STR_TO_DATE(issue_code, '%Y-%m-00%d')) = '2020'
;
UPD: Be careful with leap years
2 Comments
jarlh
Product specific answer to a question with no dbms specified. At least tell us which dbms this is for.
BARMM ICTS
Found it.. $query = UPDATE table_name SET issue_code = REPLACE(issue_code, '2020', '2021') WHERE issue_code LIKE '%2020%'
