CODEWITHSUNDEEP
mysqldatabase
Emmon
Emmon

Reputation: 385

How to make the value of another column in a table to be the value of the primary key column in the same table?

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?

Upvotes: 0

Views: 371

Answers (2)

Maksym Zapara
Maksym Zapara

Reputation: 53

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.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

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),
    ...
)

Upvotes: 1

Related Questions