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](http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) — Koryu, Dec 14 '13 at 09:11

Sanal K · Answer 1 · 2013-12-14T09:18:19.437

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 '13 at 09:18

answered Dec 14 '13 at 09:01

Sanal K

723
4
14

i want to add in delete statement with my SELECT statement – John Walker Dec 14 '13 at 09:03
@ChinYe can you check now? – Sanal K Dec 14 '13 at 09:10
this is correct, but any idea to make Select Rate_ID from tbl_Rate ? like my original SQL statement – John Walker Dec 14 '13 at 09:27

peterm · Accepted Answer · 2013-12-14T09:46:03.237

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

That should work. Also take a look at [this answer](http://stackoverflow.com/a/9843719/2546102) — Koryu, Dec 14 '13 at 09:28
@peterm Error Code: 1248. Every derived table must have its own alias — John Walker, Dec 14 '13 at 09:30
See updated answer. I've incorporated your query into a subquery. — peterm, Dec 14 '13 at 09:47

score 0 · Answer 3 · answered Dec 14 '13 at 09:10

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 
)

MySQL Delete From Select

3 Answers3