[RESOLVED] writing an INSERT ... SELECT statement

perpetualshaun

I have used this same format of an INSERT ... SELECT statment many times, and once again I'm trying to use it to creat a copy of an order. However, this query is not working on the live site. It works just fine when I run it on my machine and on another server though of course. :bemused: Here's the query:

INSERT INTO order_header( order_received_on, order_po_num )
SELECT 1130516213 , orig_order_header.order_po_num
FROM order_header AS orig_order_header
WHERE orig_order_header.order_id = 14

And on the site, it spits out this error to me:

INSERT TABLE 'order_header' isn't allowed in FROM table list

This site is running MySQL 3.23.56.

Any ideas?

Thx,
Shaun

liquorvicar

what versions of MySQL are you using? is your Live server running an older version?

perpetualshaun

Yes, the live site is running an older version.

Live: MySQL 3.23.56
Local: MySQL 4.0.20a-nt

I thought that I had been using this same kind of query for the last few years, but perhaps I'm mistaken. Can anyone think or another alternative query to write that will work in MySQL 3.23.56? The other possible solution that I can think of is reading the original record into an array and then writing the new row.

liquorvicar

From memory in MySQL 3.* you can use SELECT...INTO to achieve what i think you're trying to achieve here.

Roger_Ramjet

liquorvicar wrote:
From memory in MySQL 3.* you can use SELECT...INTO to achieve what i think you're trying to achieve here.

But not in this way

" Prior to MySQL 4.0.14, the target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query. This limitation is lifted in 4.0.14. "

Now my solution would be to change to a server with a newer version anyway - absolutely no point in using version 3 anymore. If you really cannot change servers then select out into a temp table and then select back into the real table is the only answer.