Can somebody help to rewrite this query to delete records? I having trouble getting the min(a.scancode) in some way or shape in the where clause.

select a.location, min(a.scancode) as tobedeleted
from mig_container_location_1 a
where a.location in (select b.location
                    from mig_container_location_1 b
                    where b.LOCATION like 'v%t%l%b%_____%'
                    group by b.location
                    having count(b.location) = 3)
group by a.LOCATION;

Below an example of a table. What I would like to do is delete the lowest scancode of each location.

CodePudding user response：

You shouldn't use GROUP BY in a DELETE statement. If you want to delete every record included in this statement, you can use the same WHERE clause. This would do that, however, it is a bit dangerous to blindly delete so many possible records.

DELETE FROM mig_container_location_1 WHERE scancode IN (
select min(a.scancode) as scancode
from mig_container_location_1 a
where a.location in (select b.location
    from mig_container_location_1 b
    where b.LOCATION like 'v%t%l%b%_____%'
    group by b.location
    having count(b.location) = 3)
group by a.LOCATION);

I don't have enough data to understand what your second WHERE statement is doing. It is obviously filtering for specific locations. This complicates the WHERE statement too much and may have to be done in a STORED PROCEDURE.

However, if you just want to delete the min scancode from every location, you could try this.

DELETE FROM mig_container_location_1 WHERE scancode IN (
   SELECT MIN(scancode) AS scancode 
   FROM mig_container_location_1
   GROUP BY location);

CodePudding user response：

delete from mig_container_location_1 a
where a.LOCATION like 'v%t%l%b%_____%'
  and a.scancode in (select min(b.lscancode)
                     from mig_container_location_1 b
                     where b.location = a.location
                     group by b.location
                     having count(b.location) = 3)

CodePudding user response：

You want to delete the lowest scancode row per location, provided the location matches the pattern and there are exactly 3 rows for that location. Use a tuple of location and scancode to delete the rows in question.

delete from mig_container_location_1
where (location, scancode) in 
(
  select location, min(scancode)
  from (select * from mig_container_location_1) t
  where location like 'v%t%l%b%_____%'
  group by location
  having count(*) = 3
);

I must select from (select * from mig_container_location_1) t instead of merely from mig_container_location_1 here, because MySQL doesn't allow the table you are deleting from directly in the subquery; we must put it one level deeper.