MySQL cursor - INSERT into intermediate table - 2 columns: 1 column - set value and 2 column - value from SELECT

Question

I wrote a Cursor in MySQL to insert values in 2 columns in the intermediate table dw_attended_code_events

DELIMITER //
            
CREATE PROCEDURE attended_code_events_table()
BEGIN
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE coder_ID VARCHAR(40);

DECLARE att_code_events CURSOR
FOR SELECT 'ID.x' FROM New_Coders_Survey_Data
WHERE 'ResourceBlogs' = 1;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN att_code_events;
REPEAT
    FETCH att_code_events INTO coder_ID;
    INSERT INTO dw_attended_code_events (code_event_id, coder_id) VALUES(1, coder_ID);
UNTIL done END REPEAT;
CLOSE att_code_events;
END; //
DELIMITER ;

Into code_event_id I need to insert id - which is "1" and into coder_id I need to insert the coder id that comes from table New_Coders_Survey_Data

My cursor currently inserting 1 record: 1 into 1st column and NULL into 2nd column.

How can I correct my cursor?

Kind regards, Anna

*`DECLARE att_code_events CURSOR FOR SELECT 'ID.x' FROM New_Coders_Survey_Data WHERE 'ResourceBlogs' = 1;`* - in this code `'ID.x'` and `'ResourceBlogs'` are string literals, not column names. `'ResourceBlogs' = 1` is False, cursor returns no rows, FETCH sets `coder_ID` to NULL and handler fires, NULL is inserted then the cycle breaks. — Akina, Sep 09 '20 at 18:57
*How can I correct my cursor?* Use backticks instead of quotes. But the action which you try to realize can be easily performed by one simple query, without procedure, cursor and another constructions. — Akina, Sep 09 '20 at 19:03
please run youzr select query ansee what it fgegts, you because ID.x looks wrong even when put in backticks — nbk, Sep 09 '20 at 19:37

score 0 · Answer 1 · answered Sep 09 '20 at 19:23

There's no need for the cursor. Just use the SELECT query as the source of data for INSERT. Use backticks to escape a column name that contains special characters like ., not single quotes (see When to use single quotes, double quotes, and backticks in MySQL).

INSERT INTO dw_attended_code_events (code_event_id, coder_id)
SELECT 1, `ID.x`
FROM FROM New_Coders_Survey_Data
WHERE 'ResourceBlogs' = 1;

MySQL cursor - INSERT into intermediate table - 2 columns: 1 column - set value and 2 column - value from SELECT

1 Answers1