How to change a column value to sub_string value of another column autoatically

Question

I have two columns in my mysql table, equipment and orderno, Here equipment number is manually inserted, and they are in the form C1234,C3212 etc.

I want to strip the C from equipment column and insert the remaining number to orderno column. I have seen that mysql substring_index() can effectively get substring but I am not sure how to make it automtically do the changes, when the equipment column changes.

create trigger which execute on change of equipment column update/add. check below link: http://stackoverflow.com/questions/4547465/updating-table-in-trigger-after-update-on-the-same-table — Suresh Kamrushi, Dec 28 '12 at 11:01

fthiella · Accepted Answer · 2012-12-28T11:50:42.100

You could use two triggers, one that's fired before an INSERT, and one that's fired before an UPDATE, to automatically update orderno based on equipment column:

CREATE TRIGGER upd_your_table BEFORE UPDATE ON your_table 
FOR EACH ROW
  SET new.orderno=substring(new.equipment, 2)
;
CREATE TRIGGER ins_your_table BEFORE INSERT ON your_table 
FOR EACH ROW
  SET new.orderno=substring(new.equipment, 2)
;

To update existing values, you could use this:

UPDATE your_table SET orderno=substring(equipment, 2)

See this fiddle

score 1 · Answer 2 · answered Dec 28 '12 at 11:10

1

Try this ::

UPDATE myTable set orderno= REPLACE(equipment, 'C', '')

answered Dec 28 '12 at 11:10

Sashi Kant

13,277
9
44
71

How to change a column value to sub_string value of another column autoatically

2 Answers2