#mysql #sql #syntax #sequelize.js
#mysql #sql #синтаксис #sequelize.js
Вопрос:
Я запрашиваю несколько таблиц с помощью UNION ALL , а затем использую псевдоним для вставки результата в другую таблицу. Это говорит мне, что у меня синтаксическая ошибка сразу после INSERT INTO строки. Запрос выглядит правильным для меня, и ошибка довольно расплывчата. Будем признательны за любую помощь!
SELECT
uploads.campaign_name as 'Campaign Name'
, date as Date
, country as Country
, region as Region
, amount_spent as 'Amount Spent'
, impressions as Impressions
, clicks as Clicks
, cpc as CPC
, ctr as CTR
, cpm as CPM
, category as Category
, subcategory as Subcategory
, source as Source
FROM (SELECT
facebook.ad_name as campaign_name
, facebook.day as 'date'
, CASE WHEN facebook.account_name = 'PMD Beauty' THEN 'US'
WHEN facebook.account_name = 'PMD Beauty United Kingdom' THEN 'UK'
WHEN facebook.account_name = 'PMD Beauty Australia' THEN 'AU'
WHEN facebook.account_name = 'PMD Beauty Europe' THEN 'EU' END as country
, facebook.region as region
, facebook.amount_spent as amount_spent
, facebook.impressions as impressions
, facebook.clicks as clicks
, facebook.cpc as cpc
, facebook.ctr as ctr
, facebook.cpm as cpm
, 'Facebook' as source
FROM
facebook
UNION ALL
SELECT
tiktok.campaign_name as campaign_name
, tiktok.date as 'date'
, tiktok.location as country
, null as region
, tiktok.cost as amount_spent
, tiktok.impression as impressions
, tiktok.click as clicks
, tiktok.cpc as cpc
, tiktok.ctr as ctr
, tiktok.cpm as cpm
, 'Tiktok' as source
FROM
tiktok
UNION ALL
SELECT
pinterest.ad_name as campaign_name
, pinterest.date as 'date'
, trim(substring_index(pinterest.targeting_value, ':', 1)) as country
, trim(substring_index(pinterest.targeting_value, ':', -1)) as region
, pinterest.spend_in_account_currency as amount_spent
, pinterest.impressions as impressions
, pinterest.link_clicks as clicks
, pinterest.cpc as cpc
, pinterest.ctr as ctr
, pinterest.cpm as cpm
, 'Pinterest' as source
FROM
pinterest
UNION ALL
SELECT
steelhouse.campaign_group_name as campaign_name
, steelhouse.date as 'date'
, null as country
, null as region
, steelhouse.total_spend as amount_spent
, steelhouse.impressions as impressions
, null as clicks
, null as cpc
, null as ctr
, null as cpm
, 'SteelHouse' as source
FROM
steelhouse
UNION ALL
SELECT
criteo.campaign as campaign_name
, criteo.day as 'date'
, null as country
, null as region
, criteo.cost as amount_spent
, criteo.displays as impressions
, criteo.clicks as clicks
, criteo.cpc as cpc
, criteo.ctr as ctr
, criteo.cpm as cpm
, 'Criteo' as source
FROM
criteo
UNION ALL
SELECT
hivewyre.creative_name as campaign_name
, hivewyre.date as 'date'
, 'US' as country
, null as region
, hivewyre.spend as amount_spent
, hivewyre.imps as impressions
, hivewyre.clicks as clicks
, null as cpc
, null as ctr
, null as cpm
, 'Hivewyre' as source
FROM
hivewyre
UNION ALL
SELECT
generic_social_media.campaign_name as campaign_name
, generic_social_media.date as 'date'
, generic_social_media.country as country
, generic_social_media.region as region
, generic_social_media.amount_spent as amount_spent
, generic_social_media.impressions as impressions
, generic_social_media.clicks as clicks
, generic_social_media.cpc as cpc
, generic_social_media.ctr as ctr
, generic_social_media.cpm as cpm
, 'Generic Template' as source
FROM
generic_social_media) uploads
LEFT JOIN
product_to_category
ON uploads.campaign_name LIKE BINARY concat('%', product_to_category.product, '%')
WHERE uploads.date >= :startDate
INSERT INTO
marketing (campaign_name, category, subcategory, 'date', country, region, amount_spent, impressions, clicks, cpc, ctr, cpm, source, plenadata__created_at, plenadata__updated_at)
SELECT
campaign_name
, category
, subcategory
, 'date'
, country
, region
, amount_spent
, impressions
, clicks
, cpc
, ctr
, cpm
, source
, NOW()
, NOW()
FROM
uploads
В терминале:
`error`='ttttmarketing (campaign_name, category, subcategory, 'date', country' at line 149',`stack`='SequelizeDatabaseError: You have an error in your SQL syntax;
Комментарии:
1. Базовый синтаксис
INSERT INTO tablename (columns...) SELECT .... Вы не можете поместитьINSERTпредложение послеSELECT.2. Вы не можете использовать подзапрос в качестве имени таблицы в
FROM uploads. Похоже, вы ожидаете, что предыдущийSELECTзапрос будет действовать как CTE.3. Это не отдельные операторы, между ними нет
;связи. Это один запрос, и у вас обратный синтаксис.4. Вы написали
SELECT ... INSERT INTO ... SELECT ..., что это должно быть простоINSERT INTO ... SELECT ...5. Если вы используете MySQL 8.x, вы можете использовать CTE:
WITH upload AS (SELECT ...) INSERT INTO ... SELECT ... FROM uploads