Setting one column value after another

Question

I have a table where I want one value to be set to the same as another when inserting.

Here's the table in question;

CREATE TABLE categories (  
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(30) NOT NULL,
  root INT NULL DEFAULT id,
  PRIMARY KEY(id)
);

I want the column ´root´ to get the same value as the ´id´ column gets when inserting a new row. I guess I can just do it in two queries, but I hoped I could do it in just one.

Thanks,
_L

Answer 1

I would use triggers. Do you use MySQL 5.0.2 or later? Because before that MySQL does not have triggers.

Try:

CREATE TRIGGER id_root_same AFTER INSERT ON categories 
FOR EACH ROW SET root = NEW.id;

But why would you do such a thing? It does not make sense.

Answer 2

Having a column with the exact copy of your primary key may not be a very good idea.

However you may want to check out the following articles related to your problem:

• Stack Overflow - Can you access the auto increment value in MySQL within one statement?
• MySQL Forums - Triggers - Accessing auto-increment value in Trigger