syntax-errorsqlinsert-statement

nested SELECT in INSERT sql statement


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 "');"

Solution

  • 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.