Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Error Code: 1093. You can't specify target table 'cars' for update in FROM clause when using delete query

when i use this query in mysql workbench iam getting the below error,

Error Code: 1093. You can’t specify target table ‘cars’ for update in FROM clause

i had created and inserted the table and data using the below queries

create table cars( model_id int primary key,
                   model_name varchar(100),
                   color varchar(100),
                   brand varchar(100)
                 ); 

insert into cars values(1,'Leaf','Black','Nissan'); 
insert into cars values(2,'Leaf', 'Black', 'Nissan'); 
insert into cars values(3,'Model S', 'Black', 'Tesla'); 
insert into cars values(4,'Model X', 'White', 'Tesla');
insert into cars values(5,'Ioniq 5', 'Black', 'Hyundai'); 
insert into cars values(6,'Ioniq 5', 'Black', 'Hyundai'); 
insert into cars values(7,'Ioniq 6', 'White', 'Hyundai');

delete from cars where model_id not in (
  select min(model_id) from cars group by model_name,brand
);

please help me

Tags:

2

Answers


  1. 0

    try to use SET SQL_SAFE_UPDATES = 0; before delete query and then SET SQL_SAFE_UPDATES = 1; after it.

    Login or Signup to reply.
  2. 0

    There’s a — rather obscure — MySQL syntax that can help you:

    delete cars
from cars
join (
  select model_name, brand, min(model_id) as mid
  from cars
  group by model_name, brand
) x on cars.model_name = x.model_name
   and cars.brand = x.brand
   and cars.model_id <> x.mid;

    Result:

     model_id  model_name  color  brand   
 --------- ----------- ------ ------- 
 1         Leaf        Black  Nissan  
 3         Model S     Black  Tesla   
 4         Model X     White  Tesla   
 5         Ioniq 5     Black  Hyundai 
 7         Ioniq 6     White  Hyundai

    See running example at db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search