Delete multiple rows from a table (SQL)

Question

when I am studying the lectures of database system, I tried the code provided in the lectures slides but was not working. The example is to delete some tuples from a table.

Example:Delete from Beers(name, manf) all beers for which there is another beer by the same manufacturer. And the code provided in the slide is as follows:

DELETE FROM Beers b  
WHERE EXISTS(    
   SELECT name  
   FROM Beers  
   WHERE manf = b.manf AND name <> b.name);

I create the Beers table and insert two rows as follows:

create table Beers(
    name CHAR(30) primary key,
    manf CHAR(30)
);

insert into Beers(name, manf)
values('Bud', 'A-B');

insert into Beers
values('Bud-lite', 'A-B');

and test the code. According to the lecture, it should delete all the tuples in Beers , however, the code is not working and kept showing "you can't specify target table 'Beers' for update in FROM clause. when I run the delete operation on SQL.

Can someone tell me what's wrong with the code? Thanks!

Add note: Just find out MySQL can't run the previous code but can run this one:

Delete from Beers
where exists(
  select a.name from(select b.name from Beers b
                     where manf = b.manf AND name!= b.name) as a);

But I still don't know why we need to use select twice inside exists. Could someone explain how this code works?

Possible duplicate of [Delete - I can't specify target table?](https://stackoverflow.com/questions/5816840/delete-i-cant-specify-target-table) — Parfait, Oct 21 '17 at 21:04

score 2 · Accepted Answer · answered Oct 21 '17 at 21:06

2

In mysql during update/delete you can't use the same table, Alternatively you can do the same using JOIN

DELETE a
FROM Beers a
JOIN Beers b ON a.manf = b.manf 
AND a.name <> b.name
/* WHERE a.manf = 'A-B' is not necessary */

DEMO

answered Oct 21 '17 at 21:06

M Khalid Junaid

63,861
10
90
118

So we need to use DELETE a FROM Beers a Join Beers b? Could we use only DELETE FROM on this? I am still confused when we should use DELETE FROM and when 'DELETE A FROM...' – tsen0406 Oct 21 '17 at 21:27
You just tell the query from which table to delete a record like `delete a from table a`, where a is an alias given to table – M Khalid Junaid Oct 21 '17 at 21:35
Thank you! Your code works! But just out of curiosity, is there any method to fix the original code that the lecturer provide? Using WHERE EXISTS? – tsen0406 Oct 21 '17 at 21:55
@tsen0406 . . . Your original code is standard SQL and works in almost all databases -- except MySQL. – Gordon Linoff Oct 21 '17 at 22:18
@GordonLinoff thank you! Yes I use MySQL. I modified the original code and it works. But I still don't quite know why it could work (I update the modified one in the question)... – tsen0406 Oct 22 '17 at 02:23

Delete multiple rows from a table (SQL)

1 Answers1