CODEWITHSUNDEEP
mysql
Sophivorus
Sophivorus

Reputation: 3083

Insert row with another column containing the row's id, besides the id column

Here is a minimal version of my table:

CREATE TABLE posts (
    id int unsigned PRIMARY KEY auto_increment,
    root_id int unsigned,
    parent_id int unsigned
);

The data is in a tree structure, so the parent_id field contains the obvious, and the root_id field contains the id of the root post, that is, the topmost post in the tree, the oldest ancestor in the tree.

Now, when I insert a new post that is itself the root of a new tree, the parent_id will be null, for it has no parent, but the root_id should refer to itself. Is there a way to do an insert that will put the same value of id into root_id? I'd like to avoid doing the insert, getting the new id and changing the root id with a second query, but maybe that's the only way.

Thanks in advance.

Upvotes: 0

Views: 91

Answers (3)

Michael
Michael

Reputation: 12806

You could do

INSERT INTO
  posts
SET
  root_id = (SELECT Auto_increment FROM information_schema.tables WHERE table_name = 'posts');

See fiddle.

Upvotes: 1

Sophivorus
Sophivorus

Reputation: 3083

I just thought of a neat one and tested it. It seems to work, but maybe under certain conditions it can go wrong?

INSERT INTO posts ( root_id, parent_id ) VALUES ( LAST_INSERT_ID() + 1, NULL );

Is there any problem or disadvantage to this?

Upvotes: 0

juergen d
juergen d

Reputation: 204766

You can use a trigger to update the table instantly after an insert

delimiter |
CREATE TRIGGER update_root AFTER INSERT on posts
FOR EACH ROW
BEGIN
    if NEW.parent_id IS NULL 
    then 
        SET NEW.root_id =  NEW.id;
    end if;
END
|
delimiter ;

Upvotes: 1

Related Questions