CODEWITHSUNDEEP
mysql
praveenjayapal
praveenjayapal

Reputation: 38569

Use select query inside the Insert query for the same table name

Is it possible to use a "select" query inside the "Insert" query, But the condition is i want to use the same table name for both "select" and "Insert" query.
For Example
mysql> insert into sample_elements (name,position,ownerel) values ('Namespace1', select id from sample_elements where name='Namespace1',0);

Please guide me on this.

Upvotes: 5

Views: 13453

Answers (5)

Andy Lorenz
Andy Lorenz

Reputation: 3084

here is another format that will work, using the 'SET' construct. I prefer this format for most of my SQL as the field names are paired with the values - makes maintenance much easier than field/value lists which depend totally on positioning.

INSERT INTO sample_elements SET
name = 'Namespace1',
position = (SELECT id FROM sample_elements WHERE name='Namespace1'),
ownere1 = 0;

Upvotes: -1

Bharanikumar
Bharanikumar

Reputation: 25733

I am not sure. I think this insert inside select for finding already exists or for what purpose.

Thanks

Upvotes: 0

Rahul
Rahul

Reputation: 13056

I don't know mysql but can you try the following ?

insert into sample_elements select name, id, 0 from sample_elements where name='Namespace1'

Upvotes: 0

mck89
mck89

Reputation: 19251

I think you can do it. You can use:

INSERT INTO table (..fields) SELECT ..fields.. FROM table;

Upvotes: 2

RC1140
RC1140

Reputation: 8663

Change your insert to look like the following : 

insert into sample_elements (name,position,ownerel) select 'Namespace1',id,0 from sample_elements where name='Namespace1';

Basically instead of using values only use the select statement and insert the harcoded values as columns

Upvotes: 16

Related Questions