CODEWITHSUNDEEP
mysqlsqlinsert
galica
galica

Reputation: 147

Nested insert with select

I have recursive table:

CREATE TABLE IF NOT EXISTS `Table1` (
   `Id_table1` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
   `Name` VARCHAR(45) NOT NULL ,
   `Parent_Id` INT UNSIGNED NOT NULL ,
   PRIMARY KEY (`Id_table1`) ,
   INDEX `fk_Table1_Table1` (`Parent_Id` ASC) ,
   UNIQUE INDEX `Id_UNIQUE` (`Id_table1` ASC) ,
   CONSTRAINT `fk_Table1_Table1`
      FOREIGN KEY (`Parent_Id` ) REFERENCES `Table1` (`Id_table1` )
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
   ENGINE = InnoDB;

I try to execute:

INSERT INTO table1 (`Name`, `Parent_Id`)
   VALUES ('name123', SELECT Id_table1
                      from table1
                      where table1.`Name` = 'sampleName')

Unfortunately, receives an error.

Data table: 

id: 1 name: name1   parent_id: null
id: 2 name: name11  parent_id: null
id: 3 name: name123 parent_id: null

Any idea how to execute this insert?

Upvotes: 2

Views: 6440

Answers (1)

Adam Wenger
Adam Wenger

Reputation: 17540

Try the following INSERT:

INSERT INTO table1 (Name, Parent_Id)
SELECT 'name123', t.Id_table1
FROM table1 AS t
WHERE t.Name = 'sampleName';

Upvotes: 5

Related Questions