I'm trying to run a an insert statement where it has a nested select statement. These first two work just fine:
INSERT INTO respondent SET SurveyId='462', PCode='379186', LanguagePreferenceId='1', SpecialNotes='' , SourceOfEntry='l' , StateProvinceId='5' , CompletedDt='2013-04-05 17:35:09', SurveyMethod='p', ActiveInactiveCompleted='c' , TerminationDt='2013-01-18 00:00:00' , ListDt='2013-03-28 00:00:00' , MailingDt='2013-04-02 00:00:00' , ToInterviewerDt='2013-04-11 00:00:00' , LastRevisedDt='2013-04-08 00:00:00' , HsdUserId='3423';
UPDATE respondent SET CompletedDt='2013-04-05 17:35:09' WHERE RespondentId='( SELECT RespondentId FROM respondent WHERE PCode=379186 AND SurveyId=462 )';
So I have something similar in the INSERT statement into another table in my DB. Check it out:
INSERT INTO survey_question_response SET SurveyQuestionId='294', RespondentId='( SELECT RespondentId FROM respondent WHERE PCode=379186 AND SurveyId=462 )' , QuestionOptionId='2' , ResponseDt='2013-04-05 17:35:09';
I didn't get any errors when I ran the attached file in the command line, and that's why I got excited. But then I found that all the entries in the survey_question_response from that file have a RespondentId=0.
When I tried to run that last query directly in phpMyAdmin, I get this error:
[ATTACH]5423[/ATTACH]
But of course, if I run the SELECT RespondentId FROM respondent WHERE PCode=379186 AND SurveyId=462 part of that query, I get the appropriate RespondentId.
Any ideas from the info I've shared here?
Screen Shot 2016-11-23 at 1.35.01 PM.png
