CODEWITHSUNDEEP
mysqlsql
Vlad Turak
Vlad Turak

Reputation: 6364

Insert into MySQL table with select statement

My MySQL table has three columns and ten rows:

ID     NAME     ParentID
1    Parent #1    NULL
2    Child #1      1
3    Child #1      1 
4    Child #1      1
5    Child #1      2
6    Child #1      2
7    Child #1      3
8    Child #1      3
9    Child #1      3
10   Child #1      3

After adding the new element on the front-end, I am getting only the parent's ID to which this new element belongs to.

Let's see the example. I'd like to add the new element to the Child #1 that has ID equals to 3 and ParentID equals to 1.

From the front-end, I get only the ID value (e.g. 3 in our example above). When I'd like to insert the new element into the table, I need to set this element the right ParentID. What query should I use to achieve this? Please, take a look at my query below:

INSERT INTO `tree` (`ID`, `Name`, `ParentID`) 
VALUES (11, "The new element", "and here I'd like to give this new element the ParentID of the element with ID equals to 3");

Upvotes: 1

Views: 144

Answers (4)

Alex
Alex

Reputation: 17289

http://sqlfiddle.com/#!9/c574d/1

INSERT INTO tree (id, name, parentID)
  SELECT 11, 'Inserted Element', parentID 
  FROM tree t 
  WHERE t.id = 3;

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

You can do this with insert select statement:

insert into `tree`(`ID`, `Name`, `ParentID`)
select 11, "Inserted Element", `ParentID`
from `tree` where `ID` = 3

Upvotes: 1

AvielNiego
AvielNiego

Reputation: 1243

I don't know if this syntax is legal with your DB, but try something like this

INSERT INTO `tree`(`ID`, `Name`, `ParentID`) VALUES (11, "Inserted Element", (SELECT ParentID FROM tree 
WHERE id = 3));

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44874

Usually this needs to be done via the application level however in the same query you can use insert into .. select from

insert into `tree`(`ID`, `Name`, `ParentID`)
select 
11,
"Inserted Element",
ParentID
from(
 select ParentID from tree where ID = 3
)x

Upvotes: 1

Related Questions