nested SELECT in INSERT sql statement

Question

Can someone tell me why this doesnt work?

INSERT INTO Medical_History (pid, grafts, allergies,  diseases, surgearies, treatment)
VALUES ((SELECT pid FROM Pet WHERE pet_name='Jack' AND cid=(SELECT cid FROM Customer WHERE last_name='Iwannidis' AND first_name='Giwrgos')),
       'grafts', 'allergies', 'diseases', 'surgearies', treatments');

I get a syntax error:

unrecognized token "');"

well... it does work if you add the missing ' in 'statements'. lol Gordon got a better formatted syntax tho. I grant him the answer. — lephleg, May 28 '13 at 01:46

Gordon Linoff · Accepted Answer · 2013-05-28T01:44:57.673

28

The select nested in the values statement looks wrong (and I'm not sure that all databases accept it). A more typical way to express this is:

INSERT INTO Medical_History (pid, grafts, allergies,  diseases, surgearies, treatment)
    SELECT pid, 'grafts', 'allergies', 'diseases', 'surgearies', 'treatments'
    FROM Pet
    WHERE pet_name='Jack' AND
          cid=(SELECT cid
               FROM Customer
               WHERE last_name='Iwannidis' AND first_name='Giwrgos'
              );

This is particularly important if the subquery returns more than one value. Then the query is likely to get an error.

edited May 28 '13 at 01:44

answered May 28 '13 at 01:35

Gordon Linoff

1,242,037
58
646
786

After i fixed the missing ' in 'treatments' it worked like charm. But obviously your syntax seems much more legit. Thanks for pointing that out! – lephleg May 28 '13 at 01:44
1

@LePhleg . . . I've rephrased the answer. In general, I just don't use `values` because it is totally redundant, and `select` is more versatile. – Gordon Linoff May 28 '13 at 01:46

score 2 · Answer 2 · edited Aug 21 '17 at 06:47

2

I had a syntax error because I had forgotten the quotes (') on 'treatments'.

edited Aug 21 '17 at 06:47

Cody Gray - on strike

239,200
50
490
574

answered May 28 '13 at 01:36

lephleg

1,724
2
21
41

3

true, but irrelevant. – Dan Bracuk May 28 '13 at 01:54
that was the syntax error @DanBracuk :) – lephleg May 28 '13 at 02:46

nested SELECT in INSERT sql statement

2 Answers2