[thelist] best method for copying records

[Chris W. Parker]

rudy <mailto:rudy937 at rogers.com>
    on Friday, August 29, 2003 5:17 PM said:

Follow up question:

That first query worked fine and dandy but now I'm coming against a
similar problem and now getting a sql error.

Below are the query's I've got so far. The first one works fine.

INSERT INTO savedcart
	( name
	, cust_id
	, created
	, lastaccessed )
SELECT 'test'
	, 6
	, '2003-09-02 13:14:29'
	, '2003-09-02 13:14:29'
FROM cart

INSERT INTO savedcart_contents
	( cart_id
	, prod_id
	, price
	, qty )
SELECT 
	(SELECT LAST_INSERT_ID() FROM savedcart) AS cart_id
	, prod_id
	, price
	, qty
FROM cart_contents

As it may be obvious to some of you, the error is listed as this:

"1064, You have an error in your SQL syntax near 'SELECT
LAST_INSERT_ID() FROM savedcart) AS cart_id , prod_id , price , qty FR'
at line 7"

I need to get information from TWO different tables this time instead of
just one. I need to take some old info from "cart_contents" and put it
into savedcart_contents. At the same time I need to retrieve the
LAST_INSERT_ID() from "savedcart" (which appears in the first query) and
stick it into "savedcart_contents".

I thought this would work with a sub-select (or what I understand to be
a sub-select) but alas it does not.

I should note that I've tried the second query with both this:

	(SELECT LAST_INSERT_ID() FROM savedcart) AS cart_id

and this:

	(SELECT LAST_INSERT_ID()) AS cart_id

and this:

	SELECT LAST_INSERT_ID() AS cart_id

but none of them worked.


I could do this with two queries, but I'd prefer not to if possible.
Your help is appreciated.


Thanks,
Chris.