MySQL Delete From Select

Question

This is my database table tbl_rate

delimiter $$

CREATE TABLE `tbl_rate` (
  `Rate_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Route_ID` int(11) NOT NULL,
  `From_LocationID` int(11) NOT NULL,
  `To_LocationID` int(11) NOT NULL,
  `Normal_Rate` double NOT NULL,
  `Discounted_Rate` double NOT NULL,
  PRIMARY KEY (`Rate_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=289 DEFAULT CHARSET=latin1$$

Execute SQL statement from tbl_rate

SELECT Rate_ID FROM tbl_rate WHERE 

From_LocationID NOT IN(
select 
   a.Route_LocationID from_loc_id
from tbl_route a 
inner join tbl_route b on a.Route_ID = b.Route_ID and a.Route_Seq < b.Route_Seq and a.Route_ID = 3
inner join tbl_location la on la.Location_ID = a.Route_LocationID
inner join tbl_location lb on lb.Location_ID = b.Route_LocationID
order by a.Route_Seq, b.Route_Seq)

AND To_LocationID NOT IN(
select 
   b.Route_LocationID to_loc_id
from tbl_route a 
inner join tbl_route b on a.Route_ID = b.Route_ID and a.Route_Seq < b.Route_Seq and a.Route_ID = 3
inner join tbl_location la on la.Location_ID = a.Route_LocationID
inner join tbl_location lb on lb.Location_ID = b.Route_LocationID
order by a.Route_Seq, b.Route_Seq)
AND Route_ID = 3)

this is my sql result from tbl_rate

Rate_ID
--------
253
254

i want to delete this two Rate_ID from table tbl_rate, how to add in delete SQL statement?

You cannot select and delete from the same table in the same query. Possible duplicate of Mysql error 1093 - Can't specify target table for update in FROM clause — Koryu
– Koryu, Commented Dec 14, 2013 at 9:11

peterm · Accepted Answer · 2013-12-14 09:46:03Z

1

If you want to use a subquery in DELETE with the same table you're deleting from all you have to do is to wrap your query in additional outer select

DELETE 
  FROM tbl_rate 
 WHERE Rate_ID IN
(
  SELECT Rate_ID
    FROM
  (
    SELECT Rate_ID...  --- Your original query goes here
  ) q
);

Here is SQLFiddle demo

Another option is to use multi-table DELETE syntax

DELETE t
  FROM tbl_rate t JOIN
(
  SELECT Rate_ID... --- Your original query goes here
) q
    ON t.Rate_ID = q.Rate_ID;

Here is SQLFiddle demo

Your complete DELETE statement with a subquery might look like

DELETE 
  FROM tbl_rate 
 WHERE Rate_ID IN
(
  SELECT Rate_ID
    FROM
  (
    SELECT Rate_ID 
      FROM tbl_rate 
     WHERE From_LocationID NOT IN
    (
        select a.Route_LocationID from_loc_id
          from tbl_route a 
               inner join tbl_route b 
                   on a.Route_ID = b.Route_ID and a.Route_Seq < b.Route_Seq and a.Route_ID = 3
               inner join tbl_location la 
                   on la.Location_ID = a.Route_LocationID
               inner join tbl_location lb 
                   on lb.Location_ID = b.Route_LocationID
         order by a.Route_Seq, b.Route_Seq
    )
       AND To_LocationID NOT IN
    (
        select b.Route_LocationID to_loc_id
          from tbl_route a 
               inner join tbl_route b 
                   on a.Route_ID = b.Route_ID and a.Route_Seq < b.Route_Seq and a.Route_ID = 3
               inner join tbl_location la 
                   on la.Location_ID = a.Route_LocationID
               inner join tbl_location lb 
                   on lb.Location_ID = b.Route_LocationID
         order by a.Route_Seq, b.Route_Seq
    )
       AND Route_ID = 3
   ) q
);

edited Dec 14, 2013 at 9:46

answered Dec 14, 2013 at 9:12

peterm

93k16 gold badges157 silver badges162 bronze badges

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

3 Comments

Koryu Over a year ago

That should work. Also take a look at this answer

John Walker Over a year ago

@peterm Error Code: 1248. Every derived table must have its own alias

peterm Over a year ago

See updated answer. I've incorporated your query into a subquery.

Sanal K · Accepted Answer · 2013-12-14 09:18:19Z

1

    DELETE FROM tbl_rate WHERE 

From_LocationID NOT IN(
select 
   a.Route_LocationID from_loc_id
from tbl_route a 
inner join tbl_route b on a.Route_ID = b.Route_ID and a.Route_Seq < b.Route_Seq and a.Route_ID = 3
inner join tbl_location la on la.Location_ID = a.Route_LocationID
inner join tbl_location lb on lb.Location_ID = b.Route_LocationID
order by a.Route_Seq, b.Route_Seq)

AND To_LocationID NOT IN(
select 
   b.Route_LocationID to_loc_id
from tbl_route a 
inner join tbl_route b on a.Route_ID = b.Route_ID and a.Route_Seq < b.Route_Seq and a.Route_ID = 3
inner join tbl_location la on la.Location_ID = a.Route_LocationID
inner join tbl_location lb on lb.Location_ID = b.Route_LocationID
order by a.Route_Seq, b.Route_Seq)
AND Route_ID = 3

edited Dec 14, 2013 at 9:18

answered Dec 14, 2013 at 9:01

Sanal K

7334 silver badges14 bronze badges

3 Comments

John Walker Over a year ago

i want to add in delete statement with my SELECT statement

Sanal K Over a year ago

@ChinYe can you check now?

John Walker Over a year ago

this is correct, but any idea to make Select Rate_ID from tbl_Rate ? like my original SQL statement

Edper · Accepted Answer · 2013-12-14 09:10:04Z

0

Try to add DISTINCT because mySQL is super-cautious in subqueries, like:

 SELECT DISTINCT Rate_ID FROM tbl_rate WHERE ....

So, it would be like:

DELETE FROM tbl_rate WHERE Rate_ID ( SELECT DISTINCT Rate_ID FROM tbl_rate WHERE .... )

Another way is to make an alias of your subquery like:

DELETE FROM tbl_rate WHERE Rate_ID ( 
      SELECT * FROM (SELECT Rate_ID FROM tbl_rate WHERE .... ) A 
)

answered Dec 14, 2013 at 9:10

Edper

9,3621 gold badge32 silver badges49 bronze badges

Collectives™ on Stack Overflow

MySQL Delete From Select

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related