How to make the value of another column in a table to be the value of the primary key column in the-CodePudding

I have a MySql Db_table called Branch with two fields i.e "id" and "name". id is the primary key in this table. I want the value of the "name" column to be the value passed onto the "id" column. For example, I need something like this: if name == "cmc" then id should be equal to "cmc" etc.

 id  | name

 CMC | CMC

How can I achieve this?

CodePudding user response：

Update with the following logic:

UPDATE yourTable
SET id = name
WHERE name = 'cmc';

If instead you want the name column to always reflect the value of the id, you may make name a computed column:

CREATE TABLE yourTable AS (
    id varchar(100) NOT NULL PRIMARY KEY,
    name varchar(100) AS id,
    ...
)

CodePudding user response：

Use AFTER INSERT trigger.

Something like this

    CREATE TRIGGER after_your_table_insert
    AFTER INSERT
    ON your_table FOR EACH ROW
    BEGIN
      IF NEW.name = 'CMC' THEN
        UPDATE your_table
        SET id = NEW.name;
      END IF;
    END$$

Unfortunately I don't have MySql installed on my system to check exactly this code works, but you can check some examples here.