CODEWITHSUNDEEP
mysql
MCIT Trends
MCIT Trends

Reputation: 281

MySQL update Column in increment base based on other column value

I have a MySQL table including following columns :

+------------+-------------+
|  auto_no   | auto_no_new |
+------------+-------------+
| 2021-10431 |       20577 |
| 2021-10432 |       20578 |
| 2021-10433 |       20579 |
| 2021-10434 |       20580 |
| 2021-10435 |       20581 |
| 2021-10436 |       20582 |
+------------+-------------+

Value in the "auto_no" column increments with relevant year. The values show in the table started previously and changed at the beginning of the year as above. Then I needs to start the values in the "auto_no" columns as follows :

+------------+-------------+
|  auto_no   | auto_no_new |
+------------+-------------+
| 2021-00001 |       20577 |
| 2021-00002 |       20578 |
| 2021-00003 |       20579 |
| 2021-00004 |       20580 |
| 2021-00005 |       20581 |
| 2021-00006 |       20582 |
+------------+-------------+

I used following query

update table set auto_no LIKE %'Y'- '????1'% where auto_no_new > 20577

But didn't get the desired output. What may be going wrong ? Can anyone help ?

Upvotes: 0

Views: 78

Answers (2)

FanoFN
FanoFN

Reputation: 7114

After doing a few tests, this is what I come up with:

UPDATE table1 CROSS JOIN (SELECT @rn := 0) R 
SET auto_no=CONCAT(SUBSTRING(auto_no,1,LOCATE('-',auto_no)),LPAD(@rn := @rn+1,5,0))
WHERE auto_no LIKE '2021-%';

However, I advise you to please don't run the update query without backing up your table first. I think the best way is for you to create a copy of the original table and run the update query over it rather than doing it on the original table. At least that would give you chance to do-over if something went wrong. Also, once you're satisfied with the end result (after the update), you can simply rename the original table to something like table1_original then rename the copy table as the original table.

Here is a fiddle demo

Upvotes: 1

P.Salmon
P.Salmon

Reputation: 17640

Seems trivial

drop table if exists t;
create table t(auto_no varchar(12), auto_no_new int);
insert into t values
( '2021-10431' ,       20577 ),
( '2021-10432' ,       20578 ),
( '2021-10433' ,       20579 ),
( '2021-10434' ,       20580 ),
( '2021-10435' ,       20581 ),
( '2021-10436' ,       20582 );

update t
        set auto_no = concat(substring_index(auto_no,'-',1),'-',lpad(auto_no_new - 20576,5,'0'))
where substring_index(auto_no,'-',1) = 2021;

select * from t;

+------------+-------------+
| auto_no    | auto_no_new |
+------------+-------------+
| 2021-00001 |       20577 |
| 2021-00002 |       20578 |
| 2021-00003 |       20579 |
| 2021-00004 |       20580 |
| 2021-00005 |       20581 |
| 2021-00006 |       20582 |
+------------+-------------+
6 rows in set (0.013 sec)

or if you don't know the min(auto_no_new)

update t cross join(select min(auto_no_new) - 1 minno from t where substring_index(auto_no,'-',1)  = 2021) s 
        set auto_no = concat(substring_index(auto_no,'-',1),'-',lpad(auto_no_new - s.minno,5,'0'))
where substring_index(auto_no,'-',1) = 2021;

Upvotes: 1

Related Questions