Table A [idPK,name,idFK]
insert into A (name,idFK) values ("name",<this row generated id>)
Already tried <(select (max(id)+1) from A)> but the error remains: "#1093 - You can't specify target table 'A' for update in FROM clause".
During the insert, need to know what is the corresponding id to insert as foreign key. Also the "LAST_INSERT_ID()" inserted on insert query return always zero. Doesn't work.
This works but I need to do the same but in one query on this format: [insert into A (name,idFK) values ("name",< this row generated id >)]
This works, but I need on previous format. SELECT @id := coalesce((max(id)+1),1) FROM a; INSERT INTO a(name, idFK) VALUES('AAA',@id)
Like someone seuggest, to insert first then chande FK. I cant do it. insert into a (name) values ("nameStr"); ERROR: #1452 - Cannot add or update a child row: a foreign key constraint fails (test.a, CONSTRAINT idid FOREIGN KEY (idFK) REFERENCES a (id) ON DELETE CASCADE ON UPDATE CASCADE)
You can use the LAST_INSERT_ID() function to get the ID that was generated in the most recent INSERT. So do it in two steps:
INSERT INTO A(name)
VALUES ("name");
UPDATE A
SET idFK = id
WHERE id = LAST_INSERT_ID();
But if you want to do it in one step, you could do:
INSERT INTO A(name, idFK)
SELECT "name", MAX(id) + 1
FROM A
However, this assumes that the auto increment always goes by 1. Sometimes it skips numbers.