Создайте строки данных в таблице, которые не имеют определенного значения столбца
У меня есть таблица, в которой отсутствует информация, которую необходимо вставить с очень конкретными условиями. Однако я не уверен, как действовать дальше.
У меня есть одна таблица, содержащая общее представление информации. Использование проектов в качестве примера
У меня есть еще одна таблица, содержащая часть информации из первой таблицы, в которой показаны статусы и сроки этих проектов.
Как вы можете видеть из второй таблицы, нет записи о том, когда проект B «начался», а для проекта C вообще нет записей.
Я хочу добавить записи для проектов, у которых нет статуса «начато» с произвольной датой: 01.01.2000. Таким образом, новая вторая таблица будет выглядеть так:
Первый способ, который я придумал, заключался в том, чтобы получить все проекты, которые не имеют желаемого статуса, но результаты созданного мной сценария оказались неправильными.
SELECT t.ProjectID FROM t1 t
INNER JOIN t2 tt ON t.ProjectID = tt.ProjectID
WHERE pp.ProjectStatus != 'Started'
Затем я попробовал пойти в другом направлении и нашел все проекты, имеющие желаемый статус, и мне это удалось.
SELECT ProjectStatusID, ProjectID, ProjectStatus FROM t2
WHERE ProjectStatus NOT IN (SELECT ProjectStaus FROM t2
WHERE ProjectStatus != 'Started')
Однако я не уверен, как использовать этот столбец результатов в другом скрипте. Очевидно, я мог бы создать длинную строку, используя предложение Where и скопировав и вставив все значения ProjectID, но это явно неэффективно, особенно если проектов было намного больше, чем те, которые я привел в примере выше.
Пожалуйста, покажите нам свою самую многообещающую попытку. А для более быстрых ответов предоставьте DDL+DML, который создает образцы данных - потому что мне, например, не нравится вводить все это.
@siggemannen Я хочу обновить существующую таблицу, а не создавать представление.
Хорошо, не уверен, что так оно и есть, но вам стоит проверить EXISTS, что-то вроде: SELECT ProjectID from Projects t WHERE NOT EXISTS (select 1 FROM ProjectStatus ps WHERE ps.ProjectID = t.ProjectID AND ps.Status = 'Started')
Ответы 2
Я думаю, вы просто хотите NOT EXISTS проверить, у каких проектов отсутствует статус «Начато».
insert into ProjectStatus (ProjectId, ProjectStatus, [Date])
select ProjectId, 'Started', '1/1/2000'
from Project p
where not exists (
select 1
from ProjectStatus ps
where ps.ProjectId = p.ProjectId
and ps.ProjectStatus = 'Started'
);
И если вы хотите добавить статус «Начато» только к проектам, у которых был записан хотя бы один статус, но нет статуса «Начато» (а это не то, что вы просили), вы можете просто проверить отсутствие отсутствующих ProjectStatus с использованием GROUP BY и HAVING, например.
select ProjectId, 'Started', '1/1/2000'
from ProjectStatus
group by ProjectId
having sum(case when ProjectStatus = 'Started' then 1 else 0 end) = 0;
В будущем я рекомендую добавить DBFiddle, как показано выше, чтобы людям было легче вам помогать.
Спасибо! Этот ответ помог решить мою проблему. Извините, что не предоставил больше информации. Я не пишу много вопросов на этом сайте и не взаимодействовал с такими вещами, как DBFiddle.
Этот ответ хорошо объясняет, как вставить только недостающие строки, для этого я бы использовал точно такую же команду вставки.
Просто чтобы пойти еще дальше: в вашем случае использования весьма вероятно, что вы никогда не захотите, чтобы один и тот же проект имел один и тот же статус несколько раз. Разве что проект можно «перезапустить» и закончить заново. Если да, забудьте этот ответ.
В противном случае, если проект всегда должен начинаться один раз, один раз тестироваться и один раз завершаться, это хороший вариант использования для создания такого уникального индекса:
CREATE UNIQUE INDEX UNQ_Project_Status ON ProjectStatus (ProjectId, ProjectStatus)
WITH (IGNORE_DUP_KEY = ON); -- this WITH clause is optional
Это гарантирует, что один и тот же проект не может быть запущен/тестирован/завершен несколько раз.
Обратите внимание на необязательную часть WITH (IGNORE_DUP_KEY = ON).
Если вы запускаете команды вставки и вас не волнует, какие строки будут нарушать указанный выше индекс, то это хороший вариант использования этого предложения WITH (хотя можно утверждать, что «чище» исключать дубликаты, а не позволять ограничению или индексу перехватывать их).
В этом случае можно просто выполнить команду вставки без необходимости прописывать условие, какой проект уже запущен:
INSERT INTO ProjectStatus (ProjectId, ProjectStatus, [Date])
SELECT ProjectId, 'Started', '1/1/2000' FROM Project p;
Недостающие строки будут вставлены, остальные — нет. Вам сообщат, что дубликаты были проигнорированы:
Дубликат ключа был проигнорирован. затронуты 2 строки
Если вам не нужна эта «внутренняя проверка», а вы хотите справиться с ней самостоятельно и узнать, какие команды вставки не работают, вы можете пропустить предложение WITH в определении индекса, но все равно создать индекс.
Затем вы вернетесь к команде вставки с помощью NOT EXISTS, чтобы исключить дубликаты. И вы по-прежнему будете уверены, что никогда не вставите дубликаты по ошибке благодаря индексу.
Если вы когда-нибудь забудете проверить возможные дубликаты в любой команде вставки, вы получите обычное сообщение об ошибке о том, что индекс нарушен.
Посмотрите этот образец скрипки
Прочтите документацию об этом и других параметрах индекса.
Почему бы не создать представление, в котором вы создадите фальшивую строку?