Блог

Главная - Вопросы по программированию - Как обновить базу столбцов на основе значения другого столбца из другой таблицы?

Как обновить базу столбцов на основе значения другого столбца из другой таблицы?

#sql #sql-server #tsql #sql-update

#sql #sql-сервер #tsql #sql-обновление

Вопрос:

У меня есть table A , с 2 основными столбцами: Name и Settings . Settings Столбец будет иметь 3 значения: 1, 2 или 3.

Например: 

 Name     Settings
Andre        1
Andre        1
Betty        3
Charles      1
Charles      1

Обратите внимание, что у 1 Name может быть только 1 Settings : 1, 2 или 3

У меня есть другой table B , с 2 основными столбцами: Name и Type . Type Столбец состоит из 3 значений: 'TypeA' , 'TypeB' и 'TypeC' .

Например:

 Name       Type
Andre      TypeA
Andre      TypeA
Andre      TypeC
Betty      TypeB
Betty      TypeB
Charles    TypeB
Charles    TypeA
Charles    TypeA

Обратите внимание, что 1 Name может иметь несколько одинаковых или неодинаковых значений Type . И мы не рассматриваем about TypeC , только TypeA и TypeB .

Теперь я хочу обновить Settings столбец из table A , используя 'Type' столбец из table B , если:

  • Type Столбец из table B имеет значение 'TypeA' , затем установите для всех записей, которые имеют одинаковое значение Name для обеих таблиц, значение 1 в Settings столбце из table A .
  • Type Столбец из table B имеет значение 'TypeB' , затем установите для всех записей, которые имеют одинаковое значение Name для обеих таблиц, значение 2 в Settings столбце из table A .
  • Type Столбец из table B имеет оба значения 'TypeA' и 'TypeB' , затем установите для всех записей, которые имеют одинаковое значение Name для обеих таблиц, значение 3 в Settings столбце из table A .

Результат из table A станет таким:

 Name     Settings
Andre        1
Andre        1
Andre        1
Betty        2
Charles      3
Charles      3

Ответ №1:

Похоже, вам нужна инструкция update join:

 UPDATE a
SET    settings = CASE type WHEN 'TypeA' THEN 1
                            WHEN 'TypeB' THEN 2
                  END
FROM   tablsA a
JOIN   tableB b ON a.name = b.name
WHERE  type IN ('TypeA', 'TypeB') -- just a precaution

Комментарии:

1. Крайне необходимая мера предосторожности, в противном случае другие (не совпадающие) типы будут обновлены до NULL, если вы не добавили предложение where..

2. @RaymondNijland ага, вот почему это там. Из описания операции неясно, действительно ли в таблице есть другие типы или нет. хотя.

3. Действительно, все же я бы добавил в ELSE type = type в предложении case в качестве дополнительной подстраховки для случаев, когда предложение WHERE и оператор CASE не совпадают, это может произойти при написании больших запросов.. Но все же, кто будет выполнять такого рода запросы в производственной базе данных, не протестировав их на маленькой копии производственной базы данных в качестве теста

4. @Mureinik вы правы, в столбце «Тип» больше значений, но я просто рассматриваю «TypeA» и «TypeB» для моего запроса. Также есть еще одно условие, которое я забыл добавить, просто отредактировал вопрос. В любом случае спасибо 🙂

Ответ №2:

В этом случае я вижу более одного варианта.

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

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

Если у вас разные или перекрывающиеся настройки для разных типов, возможно, также с зависимостями от пользователей, вам может понадобиться таблица сопоставления, в которой вы сопоставляете тип с настройками.

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

Это также может помочь предоставить более подробную информацию о вашем варианте использования.

Ответ №3:

Один из методов заключается в агрегировании второй таблицы перед JOIN вводом:

 UPDATE a
    SET settings = (CASE WHEN num_a > 0 AND num_b = 0 THEN 1
                         WHEN num_b > 0 THEN 2
                         ELSE 3
                    END)
FROM tablsA a JOIN
     (SELECT b.name,
             SUM(CASE WHEN type = 'TypeA' THEN 1 ELSE 0 END) as num_a,
             SUM(CASE WHEN type = 'TypeB' THEN 1 ELSE 0 END) as num_B
      FROM tableB b
      GROUP BY b.name
     ) b
     ON a.name = b.name