В SQL, как я могу добавить строку, только если ее еще нет в таблице?
Пример: у меня есть четыре объекта, где 1 имеет три foobar (два "FOO" и один "BAR"), 2 имеет foobar "FOO" (здесь нет "BAR" foobar #1), 3 имеет "BAR" и a "Доброе утро, Джон!:)" foobar, а у 4 нет (нет "BAR" foobar здесь #2). Обратите внимание, что столбец типа - это строка, не обязательно уникальная, и может содержать произвольные символы краба.
Таблица "объект":
| id | ...
+----+----
| 1 | ...
| 2 | ...
| 3 | ...
| 4 | ...
Таблица 'foobar':
| id | object_id | type | ...
+----+-----------+--------------------------+----
| 1 | 1 | "FOO" | ...
| 2 | 1 | "FOO" | ...
| 3 | 1 | "BAR" | ...
| 4 | 2 | "FOO" | ...
| 5 | 3 | "BAR" | ...
| 6 | 3 | "Good morning, John! :)" | ...
То, что я хочу сделать, это команда: Для каждого object.id, если нет foobar с object_id = object.id и foobar.type = 'BAR', добавьте в foobar (следующий идентификатор, object.id, 'BAR',...). После команды в таблицу foobar должно быть добавлено следующее:
| id | object_id | type | ...
+----+-----------+-------+----
| 7 | 2 | "BAR" | ... // adds missing #1
| 8 | 4 | "BAR" | ... // adds missing #2
Методы, которые я прочитал о вставке пропущенных столбцов, такие как INSERT IGNORE а также INSERT ... ON DUPLICATE KEY UPDATE не будет работать здесь, если я правильно понимаю, главным образом потому, что здесь тип не может быть уникальным (из-за ограничений данных).
Так что в основном мне нужно сделать соединение на несуществующей строке, чтобы добавить его, или около того. Я понятия не имею.
Я использую диалект MySQL, если это что-то помогает.
2 ответа
Так что просто найдите объекты, у которых нет 'BAR' в foobar.
А затем вставьте их в foobar.
Если foobar.id является первичным ключом с AUTO_INCREMENT, то вам не нужно вставлять идентификатор.
INSERT INTO foobar (object_id, type)
SELECT o.id, 'BAR' as Type
FROM object o
LEFT JOIN foobar f ON (f.object_id = o.id AND f.type = 'BAR')
WHERE f.id IS NULL
Используемый здесь SQL-трюк довольно старый.
Когда вы покинули стол.
Например: SELECT * FROM a LEFT JOIN b ON b.a_id = a.id
Затем он вернет все строки из "a", даже если строка не может быть связана с "b".
Затем, когда соответствующая запись в "b" отсутствует, b.id будет иметь значение NULL в наборе результатов.
Так что фильтрация на b.id IS NULL возвращает "a", у которого нет соответствующего "b".
Использование не существует:
insert into mytable (object_id, type)
select object_id, 'BAR'
from mytable t1
where type = 'FOO'
and not exists
(
select 1
from mytable t2
where t1.object_id = t2.object_id
and type = 'BAR'
)