Błąd MySQL 1093 - nie można określić tabeli docelowej dla aktualizacji w klauzuli FROM

NexusRex dostarczył bardzo dobre rozwiązanie do usuwania za pomocą join z tej samej tabeli.

Jeśli to zrobisz:

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
)

Dostaniesz błąd.

Ale jeśli zawijasz warunek w jeszcze jeden Wybierz:

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
)

Explanation: optymalizator zapytań wykonuje pochodna optymalizacja łączenia dla pierwszego zapytania (co powoduje, że nie powiodło się z błędem), ale drugie zapytanie nie kwalifikuje się do pochodnej optymalizacji merge . Dlatego optymalizator jest zmuszony najpierw wykonać zapytanie podrzędne.

inner join w zapytaniu podrzędnym jest niepotrzebne. Wygląda na to, że chcesz usunąć wpisy z story_category, Gdzie category_id nie znajduje się w tabeli category.

Zrób to:

DELETE FROM story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category);

Zamiast tego:

DELETE FROM story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category INNER JOIN
         story_category ON category_id=category.id);

Ostatnio musiałem zaktualizować rekordy w tej samej tabeli zrobiłem to jak poniżej:

UPDATE skills AS s, (SELECT id  FROM skills WHERE type = 'Programming') AS p
SET s.type = 'Development' 
WHERE s.id = p.id;

If you can ' t do

UPDATE table SET a=value WHERE x IN
    (SELECT x FROM table WHERE condition);

Ponieważ jest to ta sama tabela, możesz trick and do:

UPDATE table SET a=value WHERE x IN
    (SELECT * FROM (SELECT x FROM table WHERE condition) as t)

[update or delete or whatever]

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
)

To jest to, co zrobiłem, aby zaktualizować wartość kolumny Priorytetu o 1, jeśli jest >=1 w tabeli i w jej klauzuli WHERE, używając zapytania podrzędnego w tej samej tabeli, aby upewnić się, że co najmniej jeden wiersz zawiera Priorytet=1 (ponieważ był to warunek do sprawdzenia podczas aktualizacji):

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);

Najprostszym sposobem na to jest użycie aliasu tabeli, gdy odwołujesz się do nadrzędnej tabeli zapytań wewnątrz podrzędnego zapytania.

Przykład:

insert into xxx_tab (trans_id) values ((select max(trans_id)+1 from xxx_tab));

Zmień na:

insert into xxx_tab (trans_id) values ((select max(P.trans_id)+1 from xxx_tab P));

Dla konkretnego zapytania, które OP próbuje osiągnąć, idealnym i najbardziej efektywnym sposobem na to jest nieużywanie w ogóle zapytań podrzędnych.

Oto LEFT JOIN wersje dwóch zapytań OP:

SELECT s.* 
FROM story_category s 
LEFT JOIN category c 
ON c.id=s.category_id 
WHERE c.id IS NULL;

Uwaga: DELETE s ogranicza operacje usuwania do Tabeli story_category.
dokumentacja

DELETE s 
FROM story_category s 
LEFT JOIN category c 
ON c.id=s.category_id 
WHERE c.id IS NULL;

Można wstawić ID żądanych wierszy do tabeli tymczasowej, a następnie usunąć wszystkie wiersze, które znajdują się w tej tabeli.

Co @Cheekysoft miał na myśli, robiąc to w dwóch krokach.

Zgodnie z składnią aktualizacji Mysql podlinkowaną przez @ CheekySoft, jest napisane na dole.

Obecnie nie można zaktualizować tabeli i wybrać z tej samej tabeli w zapytaniu podrzędnym.

Domyślam się, że usuwasz z store_category, jednocześnie wybierając z niego w Unii.

Jeśli coś nie działa, przy wchodzeniu przez drzwi wejściowe, weź tylne drzwi:

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;

Spróbuj zapisać wynik instrukcji Select w osobnej zmiennej, a następnie użyj jej do zapytania delete.

Spróbuj tego

DELETE FROM story_category 
WHERE category_id NOT IN (
SELECT DISTINCT category.id 
FROM (SELECT * FROM STORY_CATEGORY) sc;

A co z tym zapytaniem mam nadzieję, że pomoże

DELETE FROM story_category LEFT JOIN (SELECT category.id FROM category) cat ON story_category.id = cat.id WHERE cat.id IS NULL

Jeśli chodzi o problem, chcesz usunąć wiersze w story_category, które nie istnieją w category.

Oto oryginalne zapytanie, aby zidentyfikować wiersze do usunięcia:

SELECT * 
FROM  story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category INNER JOIN 
       story_category ON category_id=category.id
);

Łączenie NOT IN z zapytaniem podrzędnym, które JOINwydaje się niepotrzebnie zawiłe. Można to wyrazić w bardziej prosty sposób za pomocą not exists i skorelowanej podquery:

select sc.*
from story_category sc
where not exists (select 1 from category c where c.id = sc.category_id);

Teraz łatwo przekształcić to w delete stwierdzenie:

delete from story_category
where not exists (select 1 from category c where c.id = story_category.category_id);    

To zapytanie uruchomi się na dowolnym MySQL wersji, jak również w większości innych baz danych, które znam.

Demo na DB Fiddle:

-- set-up
create table story_category(category_id int);
create table category (id int);
insert into story_category values (1), (2), (3), (4), (5);
insert into category values (4), (5), (6), (7);

-- your original query to identify offending rows
SELECT * 
FROM  story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category INNER JOIN 
       story_category ON category_id=category.id);

| category_id |
| ----------: |
|           1 |
|           2 |
|           3 |

-- a functionally-equivalent, simpler query for this
select sc.*
from story_category sc
where not exists (select 1 from category c where c.id = sc.category_id)

| category_id |
| ----------: |
|           1 |
|           2 |
|           3 |

-- the delete query
delete from story_category
where not exists (select 1 from category c where c.id = story_category.category_id);

-- outcome
select * from story_category;

| category_id |
| ----------: |
|           4 |
|           5 |