Обновить столбец с уникальным индексом, избегая дублирования SQL
У меня есть 2 таблицы, в которых мне нужно обновить один столбец записями из другой таблицы:
create table abc(id,b,c)as values
('ab1', 1, null)
,('ab2', 2, null)
,('ab3', 3, null)
,('ab4', 4, null) ;
alter table abc
add constraint abc_c_uniq unique(c)
,add constraint abc_pk primary key(id);
create table def(id,e,f,fk) as values
(1, 1, 'text1', 'ab1')
,(2, 2, 'text2', 'ab2')
,(3, 3, 'text3', 'ab3')
,(4, 3, 'text3', 'ab4') ;
alter table def
add constraint def_pk primary key(id)
,add constraint def_fk_fk foreign key (fk) references abc(id);
Мне нужно обновить значения столбца c в таблице abc значениями из f из таблицы def. Столбец c имеет unique constraint, и в таблице f есть повторяющиеся значения для столбца def, но с разными foreign keys, когда я пытаюсь выполнить запрос, я получаю ошибку:
ERROR: duplicate key value violates unique constraint "abc_c_uniq" DETAIL: Key (c)=(text3) already exists.
Вот мой запрос:
UPDATE abc
SET c = def.f
FROM def
WHERE abc.id = def.fk;
Ответы 3
Вам нужно решить, что делать в случае, если имеется несколько значений. Вот один из вариантов.
UPDATE abc AS abcTable
SET c = max( defTable.f )
FROM def as defTable
WHERE abcTable.id = abcTable.fk;
Примечание. Напоминаем, что перед запуском этого кода обязательно начните transaction, если это активный db
Итак, меня больше беспокоят дубликаты, о которых Randy тоже упоминалось. То, как я решил эту проблему, будет зависеть исключительно от того, что я делал в прошлом.
При этом, учитывая, что для f в def может быть несколько значений.
Мы можем осторожно использовать его для update, используя его только один раз в update, это будет соответствовать fk в abc.c, а также гарантирует, что мы отфильтруем дубликаты в значениях fk из таблицы def, используя либо первую, либо любую запись, например значение ID или f. в данном случае для каждого fk
Фрагмент кода, который поможет вам:
WITH f_pairs AS (
SELECT DISTINCT ON (defTable.f) defTable.fk, defTable.f
FROM def AS defTable
ORDER BY defTable.f, defTable.id
)
UPDATE abc AS abcTable
SET c = f_pairs.f
FROM f_pairs
WHERE abcTable.id = f_pairs.fk;
Аналогично @Randy's : на самом деле вам следует стремиться сократить количество рассматриваемых fk, а не f. Ваш пример по-прежнему вызывает нарушение: посмотрите это демо
Спасибо обоим за код руководства, я попробовал оба, Min и Max с подзапросом, но с обоими я получил одну и ту же ошибку: duplicate key value violates unique constraint "abc_c_uniq" , поскольку c имеет уникальное ограничение
@GeekDev Я обновил его сейчас, я пропустил болевой момент, который является упущением
@Zegarek Я не знаю, как ты можешь остановить это, спасибо
Спасибо @Zegarek, это обновленное руководство по коду работает очень хорошо, спасибо за помощь.
Вы можете использовать отдельный на , чтобы выбрать только один def.fk для использования: демо в db<>fiddle
update abc
set c = payload.f
from (select distinct on(f)f,fk
from def
order by f,fk) as payload
where abc.id = payload.fk;
select * from abc;
В противном случае, как следует из ошибки, вы в конечном итоге попытаетесь использовать два text3 из def для двух разных строк в abc, нарушая ограничение уникальности.
Идея хорошая, но она не сработает, потому что она скопировала ошибочный
WHERE abcTable.id = abcTable.fk;ОП, хотя, скорее всего, так и должно бытьWHERE abcTable.id = defTable.fk;(вfkнет столбцаabc). Как только это будет исправлено, у вас останетсяERROR: aggregate functions are not allowed in UPDATE. Вы можете исправить это с помощью CTE или подзапроса, но на самом деле вам следует стремиться уменьшить количество рассматриваемыхfk, а неf.