<?php

SELECT
p.ID as order_id,
p.post_date,
MAX( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,
MAX( CASE WHEN pm.meta_key = '_billing_phone' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_phone,
MAX( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_first_name,
MAX( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_last_name,
MAX( CASE WHEN pm.meta_key = '_billing_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_address_1,
MAX( CASE WHEN pm.meta_key = '_billing_address_2' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_address_2,
MAX( CASE WHEN pm.meta_key = '_billing_city' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_city,
MAX( CASE WHEN pm.meta_key = '_billing_state' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_state,
MAX( CASE WHEN pm.meta_key = '_billing_country' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_country,
MAX( CASE WHEN pm.meta_key = '_billing_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_postcode,
MAX( CASE WHEN pm.meta_key = '_shipping_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as shipping_first_name,
MAX( CASE WHEN pm.meta_key = '_shipping_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as shipping_last_name,
MAX( CASE WHEN pm.meta_key = '_shipping_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as shipping_address_1,
MAX( CASE WHEN pm.meta_key = '_shipping_address_2' and p.ID = pm.post_id THEN pm.meta_value END ) as shipping_address_2,
MAX( CASE WHEN pm.meta_key = '_shipping_city' and p.ID = pm.post_id THEN pm.meta_value END ) as shipping_city,
MAX( CASE WHEN pm.meta_key = '_shipping_state' and p.ID = pm.post_id THEN pm.meta_value END ) as shipping_state,
MAX( CASE WHEN pm.meta_key = '_shipping_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as shipping_postcode,
MAX( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
MAX( CASE WHEN pm.meta_key = '_order_tax' and p.ID = pm.post_id THEN pm.meta_value END ) as order_tax,
MAX( CASE WHEN pm.meta_key = '_paid_date' and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date,
max( CASE WHEN pm.meta_key = '_ref_code' and p.ID = pm.post_id THEN pm.meta_value END ) as ref_code,
( SELECT group_concat( order_item_name separator '|' ) from wp1o_woocommerce_order_items where order_id = p.ID ) as order_items

FROM wp1o_posts p 
JOIN wp1o_postmeta pm on p.ID = pm.post_id
JOIN wp1o_woocommerce_order_items oi on p.ID = oi.order_id
WHERE post_type = 'shop_order' 
AND post_status In ('wc-completed','wc-processing')
AND p.to_ping != '1'
GROUP by p.ID

------------------------------------------------------------------------------------------------------------------------------------

UPDATE wp1o_posts SET  
to_ping = ''
WHERE id='290'