УДАЛИТЬ С ПЕРЕСЕЧЕНИЕМ

У меня есть две таблицы с одинаковым количеством столбцов без первичных ключей (я знаю, что это не моя вина). Теперь мне нужно удалить все строки из таблицы A, которые существуют в таблице B (они равны, каждая с 30 столбцами).

Самый быстрый способ, который я подумал, это сделать INNER JOIN и решить мою проблему. Но условия записи для всех столбцов (беспокоясь о NULL) не элегантны (возможно, потому что мои таблицы тоже не элегантны).

Я хочу использовать INTERSECT. Я не знаю, как это сделать? Это мой первый вопрос:

Я пробовал (SQL Fiddle):

declare @A table (value int, username varchar(20))
declare @B table (value int, username varchar(20))

insert into @A values (1, 'User 1'), (2, 'User 2'), (3, 'User 3'), (4, 'User 4')
insert into @B values (2, 'User 2'), (4, 'User 4'), (5, 'User 5')

DELETE @A 
    FROM (SELECT * FROM @A INTERSECT SELECT * from @B) A

Но все строки были удалены из таблицы @A.

Это привело меня ко второму вопросу: почему команда DELETE @A FROM @B удаляет все строки из таблицы @A?


sql sql-server sql-server-2012 sql-delete intersect
person Nizam    schedule 23.06.2015    source источник
comment
Если я хорошо помню, выполнение DELETE A FROM B ничего не ограничивает, и это нормально, что оно удаляет все. вы можете ограничить, выполнив что-то вроде: DELETE A FROM B, где A.value = B.Value Добавление FROM после удаления похоже на LEFT JOIN   -  person Xavier    schedule 24.06.2015
comment
Вы имели в виду DELETE A, и это не запускается. Прямо сейчас вы удаляете @A CROSS JOIN (что-то еще). Который удаляет все, если есть хотя бы одна строка в чем-то еще. Посмотрите на план запроса, чтобы увидеть это.   -  person usr    schedule 24.06.2015

Ответы (5)


arrow_upward
16
arrow_downward

Попробуй это:

DELETE a 
FROM @A a
WHERE EXISTS (SELECT a.* INTERSECT SELECT * FROM @B)

Удалить из @A, где для каждой записи в @A есть совпадение, при котором запись в @A пересекается с записью в @B.

Это основано на запись в блоге с использованием INTERSECT для проверки неравенства.

скрипт SQL

person 8kb    schedule 24.06.2015
comment
Большое спасибо. Это синтаксис, который я искал. Трудно выбрать правильный ответ, так как я задал два вопроса. Но лучший ответ на мой второй вопрос — это комментарии @usr. - person Nizam; 24.06.2015

arrow_upward
4
arrow_downward

Чтобы ответить на свой первый вопрос, вы можете удалить на основе join:

delete a 
from @a a
join @b b on a.value = b.value and a.username = b.username

Второй случай действительно странный. Я помню подобный случай здесь и много жалоб на это поведение. Я постараюсь найти этот вопрос.

person Giorgi Nakeuri    schedule 23.06.2015
comment
Большое спасибо. Это работает, но я хочу избежать объединения из-за количества столбцов в моем реальном случае. - person Nizam; 24.06.2015

arrow_upward
3
arrow_downward

Вы можете использовать ответ Джорджи, чтобы удалить нужные вам строки.

Что касается вопроса о том, почему все строки были удалены, это потому, что нет ограничивающего условия. Ваше предложение FROM получает таблицу для обработки, но нет предложения WHERE для предотвращения удаления определенных строк из @A.

person Amit    schedule 23.06.2015
comment
Недостаточно таблицы для обработки sqlfiddle.com/#!6/9eecb7db59d16c80417c72d1/1896 - person Martin Smith; 24.06.2015
comment
Также рассмотрите возможность использования EXCEPT вместо INTERSECT, если вашей конечной целью является удаление только из одной из двух сравниваемых таблиц. Логика и шаги были бы проще. - person Emacs User; 24.06.2015
comment
@MartinSmith - в вашем образце @A пуст (удален...). Это правда, что если предложение FROM пусто, ничего не удаляется, но что касается того, что произошло в OP (где предложение FROM НЕ пусто...), причина в том, что я указал. - person Amit; 24.06.2015

arrow_upward
0
arrow_downward

  1. Создайте таблицу (T), определяющую первичные ключи
  2. вставьте все записи из A в T (я предполагаю, что в A нет дубликатов)
  3. попробуйте вставить все записи из B в T 3A. если вставка не удалась, удалите его из B (уже существует)
  4. Drop T (вы действительно не должны !!!)
person SKG    schedule 23.06.2015

arrow_upward
0
arrow_downward

Ответ Джорджи явно сравнивает все столбцы, которых вы хотели избежать. Можно написать код, который явно не перечисляет все столбцы. EXCEPT создает нужный вам набор результатов, но я не знаю, как использовать этот набор результатов для DELETE исходных строк из A без первичного ключа. Итак, приведенное ниже решение сохраняет этот промежуточный результат во временной таблице с использованием SELECT * INTO. Затем удаляет все из A и копирует временный результат в A. Оберните его транзакцией.

-- generate the final result set that we want to have and save it in a temporary table
SELECT *
INTO #t
FROM
(
    SELECT * FROM @A
    EXCEPT
    SELECT * FROM @B
) AS E;

-- copy temporary result back into A
DELETE FROM @A;

INSERT INTO @A
SELECT * FROM #t;

DROP TABLE #t;

-- check the result
SELECT * FROM @A;

набор результатов

value    username
1        User 1
3        User 3

Преимущество этого решения в том, что оно использует * вместо полного списка столбцов. Конечно, вы также можете явно перечислить все столбцы. Это все равно будет проще писать и обрабатывать, чем писать сравнения всех столбцов и заботиться о возможных NULL.

person Vladimir Baranov    schedule 24.06.2015