Need Help! MySQL error
[SQL] DROP TABLE IF EXISTS BCustOrd;
Affected rows: 0
Time: 0.172ms
[SQL]
CREATE TABLE IF NOT EXISTS BCustOrd LIKE BCustOrderStructure;
Affected rows: 0
Time: 0.187ms
[SQL]
INSERT INTO BCustOrd SELECT DISTINCT
TRUNCATE(mn6l0_redshop_orders.order_id,0) AS order_id, TRIM(mn6l0_redshop_order_item.user_info_id) AS users_info_id,
TRUNCATE(mn6l0_redshop_orders.user_id,0) as user_id, TRUNCATE(order_total,2) AS order_total,
TRUNCATE(coupon_discount,2) AS coupon_discount, TRIM(mn6l0_redshop_orders.order_status) AS order_status,
FROM_UNIXTIME(mn6l0_redshop_orders.cdate,"%m-%d-%Y"), TRIM(mn6l0_redshop_orders.customer_note) as customer_note,
TRUNCATE(product_id,0) AS product_id, TRUNCATE(order_info_id,0) as order_info_id, TRIM(firstname) AS firstname,
TRIM(lastname) AS lastname, TRIM(address_type) AS address_type, TRIM(address) AS address,
TRIM(city) AS city, TRIM(state_code) AS state_code , TRIM(zipcode) AS zipcode, TRIM(phone) AS phone, TRIM(user_email) AS user_email,
SPACE(0) AS Church_Code
FROM mn6l0_redshop_orders LEFT JOIN (mn6l0_redshop_order_item, mn6l0_redshop_order_users_info)
ON (mn6l0_redshop_orders.order_id = mn6l0_redshop_order_item.order_id AND
mn6l0_redshop_orders.order_id = mn6l0_redshop_order_users_info.order_id)
WHERE mn6l0_redshop_orders.order_status = "C" AND mn6l0_redshop_order_item.product_id < 3 AND mn6l0_redshop_orders.order_id > 9
ORDER BY users_info_id, address_type;
Affected rows: 16
Time: 0.188ms
[SQL]
UPDATE BCustOrd
SET Church_Code = UPPER(mn6l0_extendedreg_users.cf_cc_code)
WHERE mn6l0_extendedreg_users.user_id = BCustOrd.user_id;
[Err] 1054 - Unknown column 'mn6l0_extendedreg_users.user_id' in 'where clause'
This is the structure of mn6l0_extendedreg_users:
user_id acceptedterms approve overage ip_addr form_id notes last_activation_request approve_hash terminate_hash block_tstamp cf_cc_code
42 0 1 0 178.199.48.63 0/0/0000 00:00:00 0/0/0000 00:00:00
43 0 1 0 0/0/0000 00:00:00 0/0/0000 00:00:00
47 0 1 0 0/0/0000 00:00:00 0/0/0000 00:00:00
50 0 0 0 0/0/0000 00:00:00 0/0/0000 00:00:00
135 0 1 0 70.196.2.40 2 0/0/0000 00:00:00 0/0/0000 00:00:00 KUPCLA
134 0 1 0 72.146.54.212 2 0/0/0000 00:00:00 0/0/0000 00:00:00 KUPCLA
133 0 1 0 68.18.4.183 2 0/0/0000 00:00:00 0/0/0000 00:00:00 KUPCLA