MySQL-fout 1093 – Kan doeltabel voor update niet specificeren in FROM-clausule

Ik heb een tabel story_categoryin mijn database met corrupte gegevens. De volgende query retourneert de corrupte invoer:

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

Ik heb geprobeerd ze te verwijderen door:

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

Maar ik krijg de volgende foutmelding:

#1093 – U kunt de doeltabel ‘story_category’ niet specificeren voor update in de FROM-clausule

Hoe kan ik dit oplossen?

Antwoord 1, autoriteit 100%

Update: dit antwoord behandelt de algemene foutclassificatie. Voor een specifieker antwoord over hoe u de exacte vraag van de OP het beste kunt behandelen, zie andere antwoorden op deze vraag

In MySQL kunt u niet dezelfde tabel wijzigen die u in het SELECT-gedeelte gebruikt.
Dit gedrag is gedocumenteerd op:
http://dev.mysql.com/doc/refman/5.6/en /update.html

Misschien kun je gewoon aan tafel gaan zitten

Als de logica eenvoudig genoeg is om de query opnieuw vorm te geven, verliest u de subquery en voegt u de tabel aan zichzelf toe, waarbij u de juiste selectiecriteria gebruikt. Hierdoor ziet MySQL de tabel als twee verschillende dingen, waardoor destructieve veranderingen kunnen plaatsvinden.

UPDATE tbl AS a
INNER JOIN tbl AS b ON ....
SET a.col = b.col

U kunt ook proberen de subquery dieper in een from-clausule te nesten …

Als je de subquery absoluut nodig hebt, is er een tijdelijke oplossing, maar die is
lelijk om verschillende redenen, waaronder prestaties:

UPDATE tbl SET col = (
  SELECT ... FROM (SELECT.... FROM) AS x);

De geneste subquery in de FROM-component creëert een impliciete tijdelijke
table, dus het telt niet als dezelfde tabel die u bijwerkt.

… maar pas op voor de query-optimizer

Pas echter op dat van MySQL 5.7.6en hoger, kan de optimizer de subquery optimaliseren en u nog steeds de fout geven. Gelukkig kan de variabele optimizer_switchworden gebruikt om dit gedrag uit te schakelen; hoewel ik dit niet kan aanbevelen als iets meer dan een kortetermijnoplossing of voor kleine eenmalige taken.

SET optimizer_switch = 'derived_merge=off';

Met dank aan Peter V. Mørchvoor dit advies in de opmerkingen.

Voorbeeldtechniek was van Baron Schwartz, oorspronkelijk gepubliceerd op Nabble, hier geparafraseerd en uitgebreid.

Antwoord 2, autoriteit 48%

NexusRexheeft een verstrekt zeer goede oplossingvoor het verwijderen met join uit dezelfde tabel.

Als je dit doet:

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
)

u krijgt een foutmelding.

Maar als u de voorwaarde in nog een selectie plaatst:

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
)

het zou het juiste doen!!

Uitleg:de query-optimizer doet een afgeleide samenvoegoptimalisatievoor de eerste zoekopdracht (waardoor deze mislukt met de fout), maar de tweede zoekopdracht komt niet in aanmerking voor de afgeleide samenvoegoptimalisatie. Daarom wordt de optimizer gedwongen om eerst de subquery uit te voeren.

Antwoord 3, autoriteit 14%

De inner joinin uw subquery is niet nodig. Het lijkt erop dat je de items in story_categorywilt verwijderen waar de category_idniet in de category-tabel staat.

Doe dit:

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

In plaats daarvan:

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

Antwoord 4, autoriteit 13%

Onlangs moest ik records bijwerken in dezelfde tabel die ik deed zoals hieronder:

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

Antwoord 5, autoriteit 7%

Als je niet kunt doen

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

omdat het dezelfde tafel is, kun je trick and do doen:

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

[bijwerken of verwijderen of wat dan ook]

Antwoord 6, autoriteit 5%

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
)

Antwoord 7, autoriteit 2%

Dit is wat ik deed voor het bijwerken van een Prioriteit-kolomwaarde met 1 als het >=1 is in een tabel en in de WHERE-clausule met behulp van een subquery op dezelfde tabel om ervoor te zorgen dat ten minste één rij Priority=1 ( omdat dat de voorwaarde was die moest worden gecontroleerd tijdens het uitvoeren van de update) :

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

Ik weet dat het een beetje lelijk is, maar het werkt prima.

Antwoord 8

Voor de specifieke zoekopdracht die het OP probeert te bereiken, is de ideale en meest efficiënte manier om dit te doen, helemaal GEEN subquery te gebruiken.

Hier zijn de LEFT JOIN-versies van de twee zoekopdrachten van de OP:

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

Opmerking: DELETE sbeperkt verwijderbewerkingen tot de tabel story_category.
Documentatie

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

Antwoord 9

De eenvoudigste manier om dit te doen, is door een tabelalias te gebruiken wanneer u verwijst naar de bovenliggende querytabel in de subquery.

Voorbeeld:

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

Wijzig het in:

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

Antwoord 10

U kunt de ID’s van de gewenste rijen in een tijdelijke tabel invoegen en vervolgens alle rijen in die tabel verwijderen.

en dat is misschien wat @Cheekysoft bedoelde door het in twee stappen te doen.

Antwoord 11

Volgens de Mysql UPDATE-syntaxisgekoppeld door @CheekySoft , staat helemaal onderaan.

Momenteel kunt u een tabel niet bijwerken en selecteren uit dezelfde tabel in een subquery.

Ik neem aan dat je uit store_category verwijdert terwijl je er nog steeds uit selecteert in de unie.

Antwoord 12

Als iets niet werkt, wanneer u door de voorkant komt, neem dan de achterdeur:

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;

Het is snel. Hoe groter de gegevens, hoe beter.

Antwoord 13

Probeer resultaat op te slaan van SELECT-instructie in afzonderlijke variabele en gebruik dan dat voor het verwijderen van query.

Antwoord 14

Probeer dit

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

Antwoord 15

Wat dacht u van deze query hoop dat het

helpt

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

Antwoord 16

Wat betreft, wil u rijen verwijderen in story_categorydie niet bestaan ​​in category.

Hier is uw originele query om de rijen te identificeren om te verwijderen:

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

Het combineren van NOT INmet een subquery die JOINis de oorspronkelijke tabel lijkt unecessarily ingewikkeld. Dit kan worden uitgedrukt in een rechttoe rechtaan manier met not existsen een gecorreleerde subquery:

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

Nu is het gemakkelijk om dit te wenden tot een deletestatement:

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

Dit quer zou draaien op elke MySQL-versie, evenals in de meeste andere databases die ik ken.

Demo op 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;

| Categorie_ID |
| -----------: |
| 4 |
| 5 |