INSERT SELECT ON DUPLICATE не обновляется

Question

короткий

1. я бы хотел SUM столбец в TABLE_A основанный на CRITERIA X и вставить в TABLE_B.total_x
2. я бы хотел SUM столбец в TABLE_A основанный на CRITERIA Y и вставить в TABLE_B.total_y
3. Проблема: Шаг 2 не обновляется TABLE_B.total_y

ДОЛГО

TABLE_A: данные

| year | month | type | total |
---------------------------------------
| 2013 | 11    | down | 100   |
| 2013 | 11    | down | 50    |
| 2013 | 11    | up   | 60    |
| 2013 | 10    | down | 200   |
| 2013 | 10    | up   | 15    |
| 2013 | 10    | up   | 9     |

TABLE_B: структура

CREATE TABLE `TABLE_B` (
    `year` INT(4) NULL DEFAULT NULL,
    `month` INT(2) UNSIGNED ZEROFILL NULL DEFAULT NULL,
    `total_x` INT(10) NULL DEFAULT NULL,
    `total_y` INT(10) NULL DEFAULT NULL,
    UNIQUE INDEX `unique` (`year`, `month`)
)

SQL: CRITERIA_X

INSERT INTO TABLE_B (
 `year`, `month`, `total_x`
)
SELECT 
  t.`year`, t.`month`,
  SUM(t.`total`) as total_x
FROM TABLE_A t
WHERE
  t.`type` = 'down'
GROUP BY
  t.`year`, t.`month`
 ON DUPLICATE KEY UPDATE
  `total_x` = total_x
;

SQL: CRITERIA_Y

INSERT INTO TABLE_B (
 `year`, `month`, `total_y`
)
SELECT 
  t.`year`, t.`month`,
  SUM(t.`total`) as total_y
FROM TABLE_A t
WHERE
  t.`type` = 'up'
GROUP BY
  t.`year`, t.`month`
 ON DUPLICATE KEY UPDATE
  `total_y` = total_y
;

Второй SQL (CRITERIA_Y) не обновляется total_y как и ожидалось. ЗАЧЕМ?

Answer 1

Я бы сделал это по-другому

insert into TABLE_B (year, month, total_x, total_y)
select year, month
     , sum (case [type] when 'down' then [total] else 0 end) [total_x]
     , sum (case [type] when 'up' then [total] else 0 end) [total_y]
from TABLE_A
group by [year], [month]

Или с помощью двух подзапросов способ будет

insert into TABLE_B (year, month, total_x, total_y)
select coalesce(t1.year, t2.year) year
     , coalesce(t1.month, t2.month) month
     , t1.total_x total_x
     , t2.total_y total_y
from (select year, month, sum(total) total_x
         from TABLE_A where [type]='down') t1 
full outer join
     (select year, month, sum(total) total_y
         from TABLE_A where [type]='up') t2
     on t1.year = t2.year and t1.month = t2.month

Или используя союз

insert into TABLE_B (year, month, total_x, total_y)
select year, month, sum(total_x), sum(total_y)
from ( 
   select year, month, sum(total) total_x, 0 total_y
   from TABLE_A where [type]='down'
   group by year, month
   union
   select year, month, 0 total_x, sum(total) total_y
   from TABLE_A where [type]='up'
   group by year, month) t
group by year, month  

Читая спецификации на INSERT...ON DUPLICATE KEY UPDATE, я заметил это:

Если... соответствует нескольким строкам, обновляется только одна строка. В общем, вы должны стараться избегать использования предложения ON DUPLICATE KEY UPDATE для таблиц с несколькими уникальными индексами.

Так что синтаксис с составным ключом довольно громоздкий, и я бы лично не стал его использовать.