Issue with INSERT/SELECT SQL in MySQL

Question

I get a syntax error between the INSERT part and SELECT part. I have no iddea why.

INSERT INTO SCHEDULE  ( '2018-08-13', MEMBER_PROFILE.MOW_ID, 0, MEMBER_PROFILE.FUNDING, NULL,NULL,NULL, 20, NULL)

SELECT MEMBER_PROFILE.MOW_ID 
FROM
MEMBER_PROFILE
WHERE MEMBER_PROFILE.FUNDING IN (6,7,8,10,18)
AND MEMBER_PROFILE.MOW_ID NOT IN (SELECT SCHEDULE.MOW_ID FROM SCHEDULE WHERE BKFST > 0 AND DATE ='2018-08-13');

You use `MEMBER_PROFILE.FUNDING` in your insert but you do not include that field in your select statement — Vitalii Strimbanu, Sep 05 '18 at 14:38
@Jens I think he want to use `insert` with `select` as one statement — Vitalii Strimbanu, Sep 05 '18 at 14:41
What are column name in SCHEDULE table? You can do : insert into schelule (col1, col2, col3) select '2018-08-13', MEMBER_PROFILE.MOW_ID, 0, MEMBER_PROFILE.fundind from ..... — DanB, Sep 05 '18 at 14:44
Possible duplicate of [Insert into ... values ( SELECT ... FROM ... )](https://stackoverflow.com/questions/25969/insert-into-values-select-from) — xdtTransform, Feb 21 '19 at 15:34

score 2 · Answer 1 · answered Sep 05 '18 at 14:46

Your syntax is wrong. You need to merge the bracketed part of the INSERT with the result of the select:

INSERT INTO SCHEDULE
SELECT '2018-08-13', MEMBER_PROFILE.MOW_ID, 0, MEMBER_PROFILE.FUNDING, NULL,NULL,NULL, 20, NULL
FROM
MEMBER_PROFILE
WHERE MEMBER_PROFILE.FUNDING IN (6,7,8,10,18)
AND MEMBER_PROFILE.MOW_ID NOT IN (SELECT SCHEDULE.MOW_ID FROM SCHEDULE WHERE BKFST > 0 AND DATE ='2018-08-13');

See the documentation - https://dev.mysql.com/doc/refman/8.0/en/insert-select.html - for a reference for the correct syntax.

P.S. I would also advocate that you explicitly list the field names in the Schedule table so there's no problem with which field will get inserted into which column.

e.g.

INSERT INTO SCHEDULE
(Column1, Column2 ...etc
SELECT ...etc

score 0 · Answer 2 · edited Sep 05 '18 at 16:44

0

You must include a ; symbol after each query/sentence.

INSERT INTO TABLE ( ------ );
SELECT ..... FROM TABLE WHERE ...;

edited Sep 05 '18 at 16:44

Jens

67,715
15
98
113

answered Sep 05 '18 at 15:07

Jesus Uzcanga

276
2
6

Issue with INSERT/SELECT SQL in MySQL

2 Answers2