5

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.

praveenjayapal
  • 37,683
  • 30
  • 72
  • 72

5 Answers5

16

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

RC1140
  • 8,423
  • 14
  • 48
  • 71
2

I think you can do it. You can use:

INSERT INTO table (..fields) SELECT ..fields.. FROM table;
mck89
  • 18,918
  • 16
  • 89
  • 106
0

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'
Rahul
  • 12,886
  • 13
  • 57
  • 62
0

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

Thanks

Toon Krijthe
  • 52,876
  • 38
  • 145
  • 202
Bharanikumar
  • 25,457
  • 50
  • 131
  • 201
-1

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;
Andy Lorenz
  • 2,905
  • 1
  • 29
  • 29
  • -1 This solution does not work for MySQL, which the OP clearly is using. See this answer: http://stackoverflow.com/a/14302701/998919 – fgblomqvist Jun 18 '16 at 17:50
  • it DOES work on mySQL, that's where I used it. Please clarify where/how it doesn't work, as I think you'll find its your implementation/version/driver that's the problem, not mySQL. regards – Andy Lorenz Jun 20 '16 at 09:47
  • I did specify already, check the answer I linked. Please state the version of the MySQL database that you tried it on at least. – fgblomqvist Jun 20 '16 at 15:10
  • the answer you linked to is NOT for the same scenario as both my answer and the OP. There is no relevance with the answer you've linked to. I was merely pointing out that there is a different syntax which allows you to pair fields with values. But as you've asked for proof, see http://dev.mysql.com/doc/refman/5.5/en/insert.html - or 5.6, or 5.7. "SET" is a well-defined part of the syntax – Andy Lorenz Jun 22 '16 at 22:06
  • Sorry but you don't seem to get what's wrong with your answer. You can't reference table x in a sub query inside a query to table x, without a trick or two (as explained in the answer I linked). My -1 will remain, I'll leave it up to a third person to decide whether you're correct or not, whenever such a person comes along. – fgblomqvist Jun 23 '16 at 07:50