Question 386954 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql and mybatis insert into select from using foreach

I have a list of elements [a, b, c, d].
I also have a table called ticket that looks like this:

order_pnr_id             bigint unsigned  default '0'    not null,
order_id                 bigint unsigned  default '0'    not null,
reference_ticket_id      bigint unsigned  default '0'    not null,

I am going to insert data into a new table but I want to fetch the data from the ticket table and insert it directly. However, I also want to insert the list of elements [a, b, c, d] by iterating through the list.

I know I can use INSERT INTO SELECT FROM… clause, but how can I iterate through the list at the same time? Can I use foreach with the clause?

Ideally, I want this to work:

INSERT INTO new_table
(
    order_pnr_id,
    order_id,
    reference_ticket_id,
    new_element

)
<foreach item="elem" collection="elements" open="(" separator="," close=")">
SELECT
    order_pnr_id,
    order_id,
    reference_ticket_id
    #{elem}
FROM
    ticket
WHERE
    order_id = 1

</foreach>

so this inserts four rows

Tags:

3

Answers


  1. 0

    To achieve this, you can use a stored procedure or a similar construct in your database that allows for looping or iteration.

    -- Create a temporary table to hold the elements
CREATE TEMPORARY TABLE temp_elements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    element VARCHAR(255)
);

-- Insert elements into the temporary table
INSERT INTO temp_elements (element) VALUES ('a'), ('b'), ('c'), ('d');

-- Create a stored procedure to iterate over the elements and insert into new_table
DELIMITER //
CREATE PROCEDURE insert_elements()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE elem VARCHAR(255);

    -- Cursor to fetch elements from temp_elements
    DECLARE cur CURSOR FOR SELECT element FROM temp_elements;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Temporary table to hold the result of the SELECT query
    CREATE TEMPORARY TABLE temp_result (
        order_pnr_id BIGINT UNSIGNED DEFAULT '0' NOT NULL,
        order_id BIGINT UNSIGNED DEFAULT '0' NOT NULL,
        reference_ticket_id BIGINT UNSIGNED DEFAULT '0' NOT NULL,
        new_element VARCHAR(255)
    );

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO elem;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Insert into temp_result using the SELECT statement
        INSERT INTO temp_result (order_pnr_id, order_id, reference_ticket_id, new_element)
        SELECT order_pnr_id, order_id, reference_ticket_id, elem
        FROM ticket
        WHERE order_id = 1;
    END LOOP;

    CLOSE cur;

    -- Insert from temp_result into new_table
    INSERT INTO new_table (order_pnr_id, order_id, reference_ticket_id, new_element)
    SELECT order_pnr_id, order_id, reference_ticket_id, new_element FROM temp_result;

    -- Drop temporary tables
    DROP TEMPORARY TABLE temp_elements;
    DROP TEMPORARY TABLE temp_result;
END //
DELIMITER ;

-- Call the stored procedure
CALL insert_elements();
    Login or Signup to reply.
  2. 0

    Convert your list of elements [a, b, c, d] to JSON array '["a", "b", "c", "d"]'. Then use single

    INSERT INTO new_table (
    order_pnr_id,
    order_id,
    reference_ticket_id,
    new_element
    )
SELECT ticket.order_pnr_id,
       ticket.order_id,
       ticket.reference_ticket_id,
       elements.new_element 
FROM ticket
-- CROSS JOIN JSON_TABLE(#{json_elem_array},
CROSS JOIN JSON_TABLE('["a", "b", "c", "d"]',  
                      '$[*]' COLUMNS (new_element CHAR(1) PATH '$')) elements
WHERE ticket.order_id = 1

    Adjust new_element CHAR(1) to proper datatype.

    Login or Signup to reply.
  3. 0

    If you are using MySQL version 8.0, you can use WITH clause.

    INSERT INTO new_table
(
  order_pnr_id,
  order_id,
  reference_ticket_id,
  new_element
)
WITH temp_table AS (
<foreach item="elem" collection="elements" separator=" union ">
  SELECT #{elem} elem
</foreach>
)
SELECT
  order_pnr_id,
  order_id,
  reference_ticket_id
  temp_table.elem
FROM ticket
CROSS JOIN temp_table
WHERE order_id = 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search