Rewriting query to delete records

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.

In case of performance issues, I recommend this index:

create index idx on mytable (location, scancode);

It may help the inner query to filter the locations and even find their minimum scancode quickly. And it will help the outer query to find the row by location and scancode to delete.

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

EDIT: If your only other criteria is if the same location repeats 3 times, then lets add "HAVING COUNT(location) = 3". But what if there happens to be 4 or more occurrences? I think you might want to capture anything that is greater then 2. "HAVING COUNT(location) > 2"

DELETE FROM mig_container_location_1 WHERE scancode IN (
   SELECT MIN(scancode) AS scancode
   FROM mig_container_location_1
   GROUP BY location
   HAVING COUNT(location) > 2)

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)