CODEWITHSUNDEEP
mysql
Tim
Tim

Reputation: 37

Grabbing data from several tables and inserting into new table

I'd like to insert resulting data from the following statement as new rows in an existing table:

SELECT a.post_date, b.vendor_name, c.lastName, d.firstName, null as taxiGroup
FROM (select ID,post_date from wp_posts where post_type = 'shop_order') as a, 
(SELECT order_id,vendor_name FROM wp_wcpv_commissions) as b,
(SELECT post_id,meta_value as lastName  FROM wp_postmeta  where  meta_key ='_billing_last_name') as c, 
(SELECT post_id,meta_value as firstName FROM wp_postmeta WHERE  meta_key ='_billing_first_name') as d
WHERE a.ID = b.order_id and b.order_id=c.post_id and c.post_id = d.post_id;

But, if I use the form INSERT INTO taxipassengers () VALUES(<the query above>); I get You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6, whatever that's supposed to mean.

Suggestions?

Edit: At first glance, that's a double quotation mark, but after I copy/pasted it here, I see that it's two consecutive single quotation marks, which is still curious but at least makes more sense since I don't have double quotes anywhere in my query.

Upvotes: 0

Views: 53

Answers (2)

PeaceIsPearl
PeaceIsPearl

Reputation: 339

Try this, for aliasing we don't need "as"

SELECT a.post_date, b.vendor_name, c.lastName, d.firstName, null as taxiGroup
    FROM (select ID,post_date from wp_posts where post_type = 'shop_order') a, 
    (SELECT order_id,vendor_name FROM wp_wcpv_commissions)  b,
    (SELECT post_id,meta_value as lastName  FROM wp_postmeta  where  meta_key ='_billing_last_name') c, 
    (SELECT post_id,meta_value as firstName FROM wp_postmeta WHERE  meta_key ='_billing_first_name') d
    WHERE a.ID = b.order_id and b.order_id=c.post_id and c.post_id = d.post_id;

Upvotes: 0

Shadow
Shadow

Reputation: 34231

The right syntax is:

insert into taxipassengers
select ...

So, there is no values() clause.

Upvotes: 1

Related Questions