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.