3

I have a very large table called paypal_ipn_orders. In this table I have 2 important bits of information a row called item_name and a row called sort_num. I want to use certain parameters to pull out records from paypal_ipn_orders and put them into a temporary table called temp_table. I know how to select the records as follows

SELECT `item_name`, `sort_num` 
FROM `paypal_ipn_orders`
WHERE `packing_slip_printed` = 0
AND LOWER(`payment_status`) = `completed`
AND `address_name` <> ''

That query selects all the records I want to move to the temporary database I just don't know how to do that.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user1542036
  • 423
  • 2
  • 5
  • 9

1 Answers1

4

Use MySQL's Insert Into Select I added generic data types to the columns in the temp table, you'll want to find out what the actual data types are from your table and make them the same.

CREATE TEMPORARY TABLE temp_table (
    item_name varchar(50), 
    sort_num int
);

INSERT INTO temp_table (item_name, sort_num)
SELECT `item_name`, `sort_num` 
FROM `paypal_ipn_orders`
WHERE `packing_slip_printed` = 0
AND LOWER(`payment_status`) = `completed`
AND `address_name` <> ''
jon3laze
  • 3,188
  • 6
  • 36
  • 69
  • does that create the temporary table automatically? – user1542036 Aug 16 '12 at 19:12
  • No you will want to create the temporary table first and then reference it by name in your `INSERT` statement. Are familiar with how to create a temp table in MySQL? – jon3laze Aug 16 '12 at 19:13
  • 1
    Yes. If this answer helped you achieve your goal, please accept it. If not please provide information on any issues you have run into with it. – jon3laze Aug 20 '12 at 22:16
  • Well this give me a mysql error, here is how I'm doing it. `mysql_query ("CREATE TEMPORARY TABLE temp_table ( item_name TEXT, sort_num int ) INSERT INTO temp_table (item_name, sort_num) SELECT `item_name`, `sort_num` FROM `paypal_ipn_orders` WHERE `packing_slip_printed` = 0 AND LOWER(`payment_status`) = `completed` AND `address_name` <> ''" ); It gives me a syntax error at line 5 – user1542036 Aug 21 '12 at 06:04
  • Even when I try and do this through php my admin it doesn't work. Creating the temporary table and columns works but `INSERT INTO` doesn't work. I have run into this problem before what I was told was I need to use `UPDATE temp_table SET sort_num =` and so forth I just am not sure how to go about it. – user1542036 Aug 21 '12 at 06:09