Таблица указана дважды, как в качестве цели для "UPDATE", так и как отдельный источник данных в mysql

У меня есть запрос ниже в MySQL, где я хочу проверить, равны ли ID ветки и год финансового типа от Branch_master с ID ветки и годом менеджера, а затем обновить статус в таблице менеджера с идентификатором ветки в менеджере

UPDATE manager as m1 
  SET m1.status = 'Y'
  WHERE m1.branch_id IN (
    SELECT m2.branch_id FROM manager as m2 
     WHERE (m2.branch_id,m2.year) IN (
        (
          SELECT DISTINCT branch_id,year 
            FROM 'branch_master' 
           WHERE type = 'finance'
        )
     )
  )

но получаю ошибку

Таблица "m1" указана дважды, как в качестве цели для "ОБНОВЛЕНИЯ", так и в качестве отдельного источника данных

Ответы

Ответ 1

Это типичная вещь MySQL и обычно может быть обойдена путем выбора из полученной таблицы, т.е. Вместо

FROM manager AS m2

использование

FROM (select * from manager) AS m2

Полное выражение:

UPDATE manager
SET status = 'Y'
WHERE branch_id IN
(
  select branch_id
  FROM (select * from manager) AS m2
  WHERE (branch_id, year) IN
  (
    SELECT branch_id, year
    FROM branch_master
    WHERE type = 'finance'
  )
);

Ответ 2

Пытаться:

UPDATE manager as m1 
SET m1.status = 'Y'
WHERE m1.branch_id IN (
  (SELECT DISTINCT branch_id
  FROM branch_master
  WHERE type = 'finance'))
  AND m1.year IN ((SELECT DISTINCT year
  FROM branch_master 
  WHERE type = 'finance'))

Ответ 3

Попробуйте использовать оператор EXISTS:

UPDATE manager as m1 
SET m1.status = 'Y'
WHERE EXISTS (SELECT 1
              FROM (SELECT m2.branch_id             
                    FROM branch_master AS bm
                    JOIN manager AS m2
                    WHERE bm.type = 'finance' AND
                        bm.branch_id = m2.branch_id AND
                        bm.year = m2.year) AS t
              WHERE t.branch_id = m1.branch_id);

Примечание. В запросе используется дополнительный уровень вложенности, предложенный @Thorsten, поскольку средство обхода таблицы указано дважды.

Демо здесь

Ответ 4

Может быть, не решение, но некоторые мысли о том, почему это не работает в первую очередь:

Чтение данных из таблицы, а также запись данных в эту же таблицу является не совсем определенной задачей. В каком порядке следует читать и записывать данные? Следует ли учитывать вновь записанные данные при чтении их из той же таблицы? MySQL отказывается выполнять это не только из-за ограничений, но и потому, что не является четко определенной задачей.

Решения, включающие SELECT... FROM (SELECT * FROM table) AS tmp просто выгружают все содержимое таблицы во временную таблицу, которая затем может использоваться в любых дальнейших внешних запросах, таких как, например, запрос на обновление. Это заставляет порядок операций быть следующим: сначала выделите все во временную таблицу, а затем используйте эти данные (вместо данных из исходной таблицы) для обновления.

Однако, если задействованная таблица велика, то это временное копирование будет невероятно медленным. Никакие индексы никогда не ускорят SELECT * FROM table.

У меня сегодня может быть медленный день... но разве исходный запрос не идентичен этому, у которого не должно быть проблем?

UPDATE manager as m1 
SET m1.status = 'Y'
WHERE (m1.branch_id, m1.year) IN (
      SELECT DISTINCT branch_id,year 
        FROM 'branch_master' 
       WHERE type = 'finance'
    )

Ответ 5

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

Очень быстрый способ, если у вас есть огромный объем данных, это создать временную таблицу:

  1. Создать таблицу TMP

    CREATE TEMPORARY TABLE tmp_manager (branch_id bigint auto_increment первичный ключ, год дата-время ноль);

  2. Заполнить таблицу TMP

    вставить в tmp_manager (branch_id, year) выбрать branch_id, year из менеджера;

  3. Обновление с присоединением

    UPDATE менеджер как m, tmp_manager как tmp_m внутренний JOIN менеджер как man по tmp_m.branch_id = man.branch_id SET status = 'Y' ГДЕ m.branch_id = tmp_m.branch_id и m.year = tmp_m.year and m.type = 'finance ";