Holy eight pound, six ounce, newborn baby Jesus, please don't ever perform surgery on a json string.
I see that you are trying to flatten your multidimensional indexed array of indexed arrays into an indexed array.
This is far simpler, more stable, and more professionally done in your loop.
First, if you are not retaining the table column name as the key in ...
Please just delete these:
Check if link exists
I'm guessing that your IDE is creating this template for you, with the expectation that you write meaningful documentation for the parameters and the return value. You haven't done so, and the methods are self-explanatory enough that you ...
Something like this - use group by to gather all the "franchise pairs" and you count how many have locations..
SELECT X.fr, X.fr2, COUNT(X.loc) as count FROM
select T1.fr, T2.fr2, T1.loc from dfmt T1 join (select fr as fr2, loc as loc2 from dfmt) as T2 on T1.fr < T2.fr2 and T1.loc = T2.loc2
) AS X GROUP BY fr, fr2 HAVING count > 1;
If you look at the visual table diagram on the site you linked, for the Company table, the id is the primary key and therefore is guaranteed to be unique for each row in the table. name, on the other hand, is not guaranteed to be unique. So in your query, you should be grouping and deleting companies by their id, not by their name.
Your query seems to be ...
When I started using MySQL 22 years ago, I thought about doing what you are doing. I quickly decided against it -- It is too much of a "moving target", and some constructs get quite clumsy to specify.
But the coup de grâce was when I realized that the number of keystrokes would be more than simply learning and writing SQL.
Instead, I adopted a ...
Use the multi-table flavor of DELETE. Why? To avoid the "arrow". And possibly the performance will be better.
Avoid IN ( SELECT ... ); instead, try to use JOIN (or maybe LEFT JOIN). The Optimizer has been notoriously poor at optimizing IN (SELECT...). I don't know whether it will do a good job here -- please add EXPLAIN SELECT ....