Обновление: этот ответ охватывает общую классификацию ошибок. Более подробный ответ о том, как лучше всего обрабатывать точный запрос OP, см. В других ответах на этот вопрос
. В MySQL вы не можете изменить ту же таблицу, которую используете в SELECT часть. Это поведение задокументировано по адресу: http://dev.mysql.com/doc/refman/5.6/en/update.html
Возможно, вы можете просто присоединиться к таблице самим себе
Если логика достаточно простая, чтобы переформатировать запрос, потеряйте подзапрос и присоединитесь к таблице, используя соответствующие критерии выбора. Это заставит MySQL видеть таблицу как две разные вещи, позволяя деструктивным изменениям идти вперед.
UPDATE tbl AS a
INNER JOIN tbl AS b ON ....
SET a.col = b.col
Альтернативно, попробуйте вложить подзапрос глубже в предложение from ...
Если вам абсолютно необходим подзапрос, есть обходной путь, но он уродлив по нескольким причинам, включая производительность:
UPDATE tbl SET col = (
SELECT ... FROM (SELECT.... FROM) AS x);
Вложенный подзапрос в предложении FROM создает неявную временную таблицу , поэтому он не считается той же таблицей, которую вы обновляете.
... но следите за оптимизатором запросов
Однако будьте осторожны, что из MySQL 5.7.6 и далее оптимизатор может оптимизировать подзапрос , и все еще дают вам ошибку. К счастью, переменную optimizer_switch
можно использовать для отключения этого поведения; хотя я не мог рекомендовать делать это как нечто большее, чем краткосрочное исправление, или для небольших одноразовых задач.
SET optimizer_switch = 'derived_merge=off';
Благодаря Peter V. Mørch для этого совета в комментариях.
Примерный метод был от барона Шварца, , первоначально опубликованного в Nabble , перефразированного и расширенного здесь.
NexusRex предоставил очень хорошее решение для удаления с соединением из той же таблицы.
Если вы это сделаете:
DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id AS cid FROM category
INNER JOIN story_category ON category_id=category.id
)
вы получите ошибку.
Но если вы завернете условие в еще один select
DELETE FROM story_category
WHERE category_id NOT IN (
SELECT cid FROM (
SELECT DISTINCT category.id AS cid FROM category
INNER JOIN story_category ON category_id=category.id
) AS c
)
, он поступил бы правильно!
Объяснение: Оптимизатор запросов выполняет полученную оптимизацию слияния для первого запроса (что приводит к сбою с ошибкой), но второй запрос не подходит для оптимизация слияния , поэтому оптимизатор вынужден сначала выполнить подзапрос.
Недавно мне пришлось обновлять записи в той же таблице, я сделал это, как показано ниже:
UPDATE skills AS s, (SELECT id FROM skills WHERE type = 'Programming') AS p
SET s.type = 'Development'
WHERE s.id = p.id;
В соответствии с синтаксисом Mysql UPDATE , связанным с @CheekySoft, он говорит прямо внизу.
В настоящее время вы не можете обновить таблицу и выбрать один и тот же table в подзапросе.
blockquote>Я думаю, что вы удаляете из store_category, все еще выбирая из него в объединении.
Попробуйте сохранить результат оператора Select в отдельной переменной, а затем используйте это для запроса на удаление.
как насчет этого запроса надеюсь, что он поможет
DELETE FROM story_category LEFT JOIN (SELECT category.id FROM category) cat ON story_category.id = cat.id WHERE cat.id IS NULL
DELETE FROM story_category
WHERE category_id NOT IN (
SELECT cid FROM (
SELECT DISTINCT category.id AS cid FROM category INNER JOIN story_category ON category_id=category.id
) AS c
)
Самый простой способ сделать это - использовать псевдоним таблицы, когда вы ссылаетесь на родительскую таблицу запросов внутри подзапроса.
Пример:
insert into xxx_tab (trans_id) values ((select max(trans_id)+1 from xxx_tab));
Измените его на:
insert into xxx_tab (trans_id) values ((select max(P.trans_id)+1 from xxx_tab P));
Это то, что я сделал для обновления значения столбца Priority на 1, если он = = 1 в таблице и в его предложении WHERE, используя подзапрос в той же таблице, чтобы удостовериться, что хотя бы одна строка содержит Priority = 1 (потому что это условие, которое нужно проверить при выполнении обновления):
UPDATE My_Table
SET Priority=Priority + 1
WHERE Priority >= 1
AND (SELECT TRUE FROM (SELECT * FROM My_Table WHERE Priority=1 LIMIT 1) as t);
Я знаю, что это немного уродливо, но все работает отлично.
попробуйте это
DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM (SELECT * FROM STORY_CATEGORY) sc;
Если вы не можете сделать
UPDATE table SET a=value WHERE x IN
(SELECT x FROM table WHERE condition);
, потому что это та же таблица, вы можете обмануть и сделать:
UPDATE table SET a=value WHERE x IN
(SELECT * FROM (SELECT x FROM table WHERE condition) as t)
[обновление или удаление или что-то еще]
inner join
в вашем суб-запросе не требуется. Похоже, вы хотите удалить записи в story_category
, где category_id
не находится в таблице category
.
Сделайте это:
DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM category);
Вместо этого :
DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM category INNER JOIN
story_category ON category_id=category.id);
Если что-то не срабатывает, когда выходите через входную дверь, тогда возьмите заднюю дверь:
drop table if exists apples;
create table if not exists apples(variety char(10) primary key, price int);
insert into apples values('fuji', 5), ('gala', 6);
drop table if exists apples_new;
create table if not exists apples_new like apples;
insert into apples_new select * from apples;
update apples_new
set price = (select price from apples where variety = 'gala')
where variety = 'fuji';
rename table apples to apples_orig;
rename table apples_new to apples;
drop table apples_orig;
Это быстро. Чем больше данных, тем лучше.
Вы можете вставить идентификаторы нужных строк в временную таблицу, а затем удалить все строки, найденные в этой таблице.
, что может быть тем, что @Cheekysoft означало, делая это за два шага.