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? — Parfait
– Parfait, Commented Oct 21, 2017 at 21:04

M Khalid Junaid · Accepted Answer · 2017-10-21 21:06:58Z

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, 2017 at 21:06

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

tsen0406 Over a year ago

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...'

M Khalid Junaid Over a year ago

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

tsen0406 Over a year ago

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?

Gordon Linoff Over a year ago

@tsen0406 . . . Your original code is standard SQL and works in almost all databases -- except MySQL.

tsen0406 Over a year ago

@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)...

Collectives™ on Stack Overflow

Delete multiple rows from a table (SQL)

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related