Nested insert with select

Question

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?

is the sampleName a value in table1? before the insert and has it been committed so that it can be selected? — xQbert
– xQbert, Commented Dec 3, 2011 at 17:49
It would also help when you have a question about a query that throws an error, that you also include the error with your question. — Adam Wenger
– Adam Wenger, Commented Dec 3, 2011 at 17:50

Adam Wenger · Accepted Answer · 2011-12-03 17:53:27Z

5

Try the following INSERT:

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

edited Dec 3, 2011 at 17:53

answered Dec 3, 2011 at 17:46

Adam Wenger

17.7k8 gold badges55 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Nested insert with select

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related