CODEWITHSUNDEEP
phpmysql
Ozzy
Ozzy

Reputation: 8322

Copy multiple rows from one table to another, directly in MySQL?

I am trying to copy values directly from one mysql table to another... but the trouble is, there are many rows to copy, so that sub-select statement will return many rows but the param :order_id is just written once.

Given that problem, I'm having trouble figuring out how to write this process:

# 1: Create new Order & Copy the cart basket to the Order basket
$order_id = addOrder($customer_id);
if ($order_id > -1) {
    $sql = "INSERT INTO tbl_order_basket(order_id, product_id, product_name,
                                         basket_qty, basket_price, subtotal)
                 VALUES (:order_id, (SELECT (cart.product_id, product.name,
                                     cart.cart_qty, cart.cart_price, cart.subtotal)
                                FROM tbl_shopping_cart AS cart
                          INNER JOIN tbl_product AS product
                                  ON cart.product_id = product.id
                               WHERE cart.php_sesskey = :session_id)
                        )
                  WHERE order_id=:order_id;";
}

Here is the data I'm trying to copy:

ORDER BASKET            CART BASKET         PRODUCTS
FK  order_id            FK  php_sesskey
FK  product_id      <-- FK  product_id  === PK  id
    product_name    <--                 <--     name
    basket_qty      <--     cart_qty
    basket_price    <--     cart_price                              # <-- : copy to
    subtotal        <--     subtotal                                # === : join on

How should I go about doing this?

Note: I'm using PHP 5 and MySQL 5.5

Upvotes: 2

Views: 7186

Answers (3)

jason
jason

Reputation: 1255

Try using the query below; this type of syntax has worked for me in the past. Since there is no WHERE in a MySQL INSERT statement (manual) I have removed that part. Also, do not use the VALUES keyword when doing an INSERT...SELECT (manual).

$sql = '
    INSERT INTO tbl_order_basket(order_id, product_id, product_name,
                                     basket_qty, basket_price, subtotal)
      SELECT :order_id, cart.product_id, product.name,
             cart.cart_qty, cart.cart_price, cart.subtotal
      FROM tbl_shopping_cart AS cart
        INNER JOIN tbl_product AS product
        ON cart.product_id = product.id
      WHERE cart.php_sesskey = :session_id)
  ';

Upvotes: 1

Tom van der Woerdt
Tom van der Woerdt

Reputation: 29985

Maybe you can use this one?

INSERT INTO ... SELECT ... FROM ... WHERE ...

To create the query, first make a SELECT which gives you all the fields which you want to insert. Then prepend INSERT INTO <table> and you're done.

Upvotes: 4

John Conde
John Conde

Reputation: 219934

Look into SELECT INTO TABLE 

INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

Upvotes: 1

Related Questions