Inserting a range of values (MySQL)

Question

I want to insert a range of values into my table

create TEMPORARY TABLE IF NOT EXISTS currency_numbers(
    num INT
);
insert into currency_numbers(num) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

insert into pm_transaction_type (tran_type, fixed, rate, min_by_rate, max_by_rate, round, min_amount, max_amount, country_code, valid_since, valid_till, currency)
        select 'fee.transfer_other_free', null, 0.1, 0, null, 0, null, null, "", null, null, currency_numbers.num
        from currency_numbers
        union
        select 'fee.transfer_tip_free', null, 0.3, 4, null, 5, null, null, "", null, null, currency_numbers.num
        from currency_numbers;

In this example, I am using the union on 2 select statements, but I want to use union with about 10. Unfortunately, I am getting the following error:

Can't reopen table: 'currency_numbers'

How can I write this query, hopefully without having to repeat either the numbers of the list of attributes names each time?

Devart · Accepted Answer · 2012-01-03 09:18:36Z

1

That is a temporary table problem - You cannot refer to a TEMPORARY table more than once in the same query.

TEMPORARY Table Problems

As a workaround, try this one -

SELECT t.*, c.* FROM currency_numbers c, (
  SELECT 'fee.transfer_other_free' tran_type, null fixed, 0.1 rate, 0 min_by_rate, null max_by_rate, 0 round, null min_amount, null max_amount, '' country_code, null valid_since, null valid_till
    UNION
  SELECT 'fee.transfer_tip_free', null, 0.3, 4, null, 5, null, null, '', null, null
  ) t;

...and its INSERT version -

INSERT INTO pm_transaction_type (tran_type, fixed, rate, min_by_rate, max_by_rate, round, min_amount, max_amount, country_code, valid_since, valid_till, currency)
  SELECT t.*, c.* FROM currency_numbers c, (
    SELECT 'fee.transfer_other_free' tran_type, null fixed, 0.1 rate, 0 min_by_rate, null max_by_rate, 0 round, null min_amount, null max_amount, '' country_code, null valid_since, null valid_till
      UNION
    SELECT 'fee.transfer_tip_free', null, 0.3, 4, null, 5, null, null, '', null, null
    ) t;

answered Jan 3, 2012 at 9:18

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Casebash Over a year ago

I just thought I'd add a not that the reason the first select clause needs labels is that otherwise MySQL would complain about duplicate null columns

Lajos Arpad · Accepted Answer · 2012-01-03 08:05:53Z

0

I didn't test this, but you get the idea:

insert into pm_transaction_type (tran_type, fixed, rate, min_by_rate, max_by_rate, round, min_amount, max_amount, country_code, valid_since, valid_till, currency)
        select 'fee.transfer_other_free', null, 0.1, 0, null, 0, null, null, "", null, null, currency_numbers.num
        from currency_numbers cn1
        union
        select 'fee.transfer_tip_free', null, 0.3, 4, null, 5, null, null, "", null, null, currency_numbers.num
        from currency_numbers cn2;

answered Jan 3, 2012 at 8:05

Lajos Arpad

80.5k42 gold badges124 silver badges235 bronze badges

Collectives™ on Stack Overflow

Inserting a range of values (MySQL)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related