How to change a query so that it is executed for each row of table X?

Question

I have the following query of the working database:

SET @orderid := '1';

INSERT INTO `cart_lines` ( `line_id` , `cart_id` , `book_id` , `book_reference` , `book_name` , `book_type` , `unit_price` , `quantity` , `date_created` , `date_updated` ) 
SELECT
  A.id AS line_id,
  CONCAT( @orderid ) AS cart_id,
  A.pid AS book_id,
  C.ref AS book_reference,
  C.name_prt AS book_name,
  concat( 'impresso' ) AS book_type,
  A.unit_price AS unit_price,
  A.qtd AS quantity,
  D.end_time AS date_created,
  D.end_time AS dade_updated
FROM table_eshop_lines A
INNER JOIN table_books C ON ( A.pid = C.id ) 
INNER JOIN table_eshop D ON ( D.id = @orderid ) 
WHERE INSTR( (
  SELECT concat( ';', lines_id, ';' ) AS ids
  FROM table_eshop
  WHERE id =@orderid ) , concat( ';', A.id, ';' ) 
) > 0

In its current form, it is necessary to update the value of the variable orderid with the value of the column id table table_esop for each existing line (manual process).

Question

How can I change this query so that it is executed by each row of the table table_eshop ?

---

^{Notes:
All the necessary data are in this query, but if it is necessary to present additional information about the tables, please leave a comment on the question and I will act accordingly.}

Answer 1

I think the query can be simplified like this (or I misunderstood the context):

INSERT INTO `cart_lines` ( `line_id` , `cart_id` , `book_id` , `book_reference` , `book_name` , `book_type` , `unit_price` , `quantity` , `date_created` , `date_updated` ) 
SELECT
  A.id AS line_id,
  D.id AS cart_id,
  A.pid AS book_id,
  C.ref AS book_reference,
  C.name_prt AS book_name,
  concat( 'impresso' ) AS book_type,
  A.unit_price AS unit_price,
  A.qtd AS quantity,
  D.end_time AS date_created,
  D.end_time AS dade_updated
FROM table_eshop_lines A
INNER JOIN table_books C ON ( A.pid = C.id ) 
INNER JOIN table_eshop D 
ON FIND_IN_SET(A.id, REPLACE(D.lines_id, ';', ',')) > 0
WHERE 1=1;

Simplified demo in SQL Fiddle.

Answer 2

One possibility is to use a CURSOR.

Below is an example of how your code could be implemented using a Cursor within a PROCEDURE.

-- Não testado
DELIMITER $$

CREATE PROCEDURE insert_tb_eshop()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE orderid INT;
  DECLARE cur1 CURSOR FOR SELECT id FROM table_eshop;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;

  read_loop: LOOP
    FETCH cur1 INTO orderid;

    IF done THEN
      LEAVE read_loop;
    END IF;

    INSERT INTO `cart_lines` ( `line_id` , `cart_id` , `book_id` , `book_reference` , `book_name` , `book_type` , `unit_price` , `quantity` , `date_created` , `date_updated` ) 
    SELECT
      A.id AS line_id,
      CONCAT( @orderid ) AS cart_id,
      A.pid AS book_id,
      C.ref AS book_reference,
      C.name_prt AS book_name,
      concat( 'impresso' ) AS book_type,
      A.unit_price AS unit_price,
      A.qtd AS quantity,
      D.end_time AS date_created,
      D.end_time AS dade_updated
    FROM table_eshop_lines A
    INNER JOIN table_books C ON ( A.pid = C.id ) 
    INNER JOIN table_eshop D ON ( D.id = @orderid ) 
    WHERE INSTR( (
      SELECT concat( ';', lines_id, ';' ) AS ids
      FROM table_eshop
      WHERE id = @orderid ) , concat( ';', A.id, ';' ) 
    ) > 0
  END LOOP;

  CLOSE cur1;
END$$

DELIMITER ;