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

2

I have the following database query working:

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 id column of the table_esop table for each existing line (manual process) . >

Question

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

Notes:
All the required data is in this query, but if you need to submit additional information about the tables, please leave a comment on the question and I will act accordingly.

    
asked by anonymous 26.12.2013 / 17:55

2 answers

2

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.

    
26.12.2013 / 23:21
1

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 ;
    
26.12.2013 / 18:21