Issue with a subquery to insert a value in a table from the same table

Question

I want to insert a value to a table where parent value for all values being inserted is some entry from the same table "master".

I used the subquesry with aliases as suggested on INSERT INTO with SubQuery MySQL

INSERT INTO mdl_question_categories (NAME, stamp, parent, info) 
SELECT 
  'cat100' AS NAME,
  'localhost+140117065545+AUXF' AS stamp,
  id 
FROM
  mdl_question_categories 
WHERE NAME = 'master',
  'desc' AS info ;

This code throws an error

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''desc' as info' at line 1 SQLState: 42000 ErrorCode: 1064

Then I changed the query to

INSERT INTO mdl_question_categories (NAME, stamp, info, parent) 
SELECT 
  'cat100' AS NAME,
  'localhost+140117065545+AUXF' AS stamp,
  'desc' AS info,
  id 
FROM
  mdl_question_categories 
WHERE NAME = 'master' ;

This works excellent.

Now the question is why did this happen?

Is there any restriction that we should always keep the value picking sql part id from mdl_question_categories where name='master' at the end of query part?

Now the question is - what do you want from `AS` (aliasing) in `WHERE` clause ? — Alma Do, Jan 17 '14 at 13:22
@Strawberry irrelevant, that is string literal in case of query above (it's enclosed by quotes) — Alma Do, Jan 17 '14 at 13:23
Ahaaaa!!!! @AlmaDo you pointed something very important that I was ignoring +1 to you. — veer7, Jan 17 '14 at 13:29
`SELECT 'cat100' AS NAME, 'localhost+140117065545+AUXF' AS stamp, (id FROM mdl_question_categories WHERE NAME = 'master') as parent, 'desc' AS info ;` — Linga, Jan 17 '14 at 13:29

Aziz Shaikh · Answer 1 · 2014-01-17T13:38:53.527

4

You are INSERTing 4 values: name,stamp,info, parent

In your old query, you were SELECTing 3 values 'cat100' as name,'localhost+140117065545+AUXF' as stamp, id. Whereas 'desc' AS info at the end was considered to be the part of WHERE clause (and not part of the SELECT) thus producing an error.

However in your new query you are correctly SELECTing 4 values (which matches the INSERT): 'cat100' AS NAME, 'localhost+140117065545+AUXF' AS stamp, 'desc' AS info, id

edited Jan 17 '14 at 13:38

answered Jan 17 '14 at 13:24

Aziz Shaikh

16,245
11
62
79

1

Nope, In first query also the op selects 4 . `SELECT 'cat100' AS NAME, 'localhost+140117065545+AUXF' AS stamp, id FROM mdl_question_categories WHERE NAME = 'master', 'desc' AS info ;`. The actual mistake was the OP missed column aliasing or he/she should surround with () – Linga Jan 17 '14 at 13:28

score 1 · Answer 2 · edited Jan 17 '14 at 13:27

1

Here I modified the query

insert into mdl_question_categories (name,stamp,parent,info)
select 'cat100' as name,
'localhost+140117065545+AUXF' as stamp,
id from mdl_question_categories
where name in('master','desc')

B'coz if you are giving the where condition like that it won't work.

edited Jan 17 '14 at 13:27

Linga

10,379
10
52
104

answered Jan 17 '14 at 13:26

RickyRam

181
1
1
10

no I didn't mean `where name in ('master','desc')`; `info` value is `'desc'` in the insert query – veer7 Jan 17 '14 at 13:41

score 1 · Answer 3 · answered Jan 17 '14 at 13:46

You need to put all the values on the select line. You seem to have moved one value after the query. Try this:

INSERT INTO mdl_question_categories (NAME, stamp, parent, info) 
    SELECT 'cat100' AS NAME, 'localhost+140117065545+AUXF' AS stamp, id , 'desc' AS info
    FROM
      mdl_question_categories 
    WHERE NAME = 'master'

Issue with a subquery to insert a value in a table from the same table

3 Answers3