Как убрать подзапрос?

Question

[Назар Мокринский]

Есть запрос:

UPDATE `table` SET `category` = '0' WHERE `category` = '1' AND `id` NOT IN (SELECT `id` FROM `table` WHERE `category` = '0')

То есть нужно по возможности сменить category c 1 на 0, но только для тех записей, где category не равно 0, чтобы не было дублей.

Таблица состоит из id и category, индекс PRIMARY на две колонки. Нужен вариант который подойдет к большинству SQL БД. Думал с FULL OUTER JOIN, но MySQL такое не поддерживает, с UNION будет медленно.

Answer 1

[Wott]

Индекс на два поля -> уникальный и update ignore

Comments

[Назар Мокринский]

Спасибо, это подходит максимально. А как на счёт других субд, на сколько широко это поддерживается?

[Wott]

Оракл так тоже сработает, остальные не в курсе, но по идее должны, если есть уникальные индексы и ignore для update.

Но по опыту нальзя строить совместимый с насколькими базами движек не абстрагируясь на уровень выше SQL запросов. Например в mysql при вставке всегда можно получить id для autoindex поля, а в оракл надо дополнять запрос хитрой конструкцией, или в mysql есть limit, а оракл имеет rownum и так далее. Сделать запрос совместимым очень сложно если это не простой select даже join уже будет по разному выглядеть и всегда по разному работать.

Answer 2

[sl_bug]

Либо я не понял, либо

UPDATE `table` SET `category` = '0' WHERE `category` = '1' 

то что вам нужно

Comments

[Назар Мокринский]

но только для тех записей, где category не равно 0, чтобы не было дублей

[Назар Мокринский]

Индекс не позволит создать дубль, если на то пошло. Индекс по двум колонкам.

[sl_bug]

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

UPDATE `test` t1 SET `category` = 0WHERE `category` = 1 AND NOT EXISTS (SELECT * FROM `test` WHERE `category` = 0 AND t1.id = id);

[sl_bug]

а не. так не прокатит. спать нужно.

[sl_bug]

UPDATE `test` t1 
left join test t2
on t1.id = t2.id and t1.category <> t2.category
SET t1.`category` = 0
where t2.category is null;

вот

[sl_bug]

sqlfiddle.com/#!2/9df67/2/0

[Назар Мокринский]

Немного не то, но ближе к цели. id могут быть любыми INT числами, category так же само, а не только 0/1.
Плюс у вас не учитывается WHERE `category` = '1'

[sl_bug]

UPDATE `test` t1 
left join test t2
on t1.id = t2.id and t1.category <> t2.category
SET t1.`category` = 0
where t2.category is null and t1.category = 1;

забыл про category = 1, все остальное будет работать.

[Назар Мокринский]

Для id = 3 не сработало
http://sqlfiddle.com/#!2/68a4f

[sl_bug]

ах. очепяточка

UPDATE `test` t1 
left join test t2
on t1.id = t2.id and t1.category > t2.category
SET t1.`category` = 0
where t2.category is null and t1.category = 1;

[Назар Мокринский]

Опять. Id = 1 и id = 3
http://sqlfiddle.com/#!2/80255/1
А как этот пример будет вести себя в сравнении с update ignore?

[sl_bug]

задача была сменить с 1 на 0. с другими числами такая магия не прокатит.

[sl_bug]

в сравнении без понятия. данный запрос работает по логике как вот такой

UPDATE `test` t1 
JOIN (
  SELECT id, MIN(category) AS category
  FROM test
  GROUP BY id) AS t2
ON t1.id = t2.id AND t1.category = t2.category
SET t1.`category` = 0
WHERE t1.category = 1;

[Назар Мокринский]

В любом случае спасибо за примеры!

[sl_bug]

с 1 на 5

UPDATE `test` t1 
left join test t2
on t1.id = t2.id and t1.category - 5 > t2.category
SET t1.`category` = 5
where t2.category is null and t1.category = 1;

[sl_bug]

точнее
on t1.id = t2.id and t1.category > t2.category-5

Answer 3

[gelas]

Что-то, по-моему, Вы перемудрили, WHERE `category` = '1' итак отсекает те записи где `category` = '0'
вполне достаточно UPDATE `table` SET category = 0 WHERE category = 1

Comments

[Назар Мокринский]

но только для тех записей, где category не равно 0, чтобы не было дублей

То есть в таблице могут быть записи с одним id, но разными category. Если запись с category = 0 не найдена — меняем 1 на 0, иначе оставляем как есть.