Mysql query to Remove similar looking column rows without data loss in 2nd table

I have 2 tables : companies and company_details. where company_details is connected by companies table with companyID. I want to remove similar looking company name and keep the lowest id value incompany table and also update the companyId in the 2nd table.

Is this possible to do ? Is there any MYSQL Query that can achieve this ?

My Original Table :

1st Table: Companies
-----------------------------------------------------------------------------------
id	|	name		|	created_at		|	updated_at
-----------------------------------------------------------------------------------
10	|	Ions Comps	|	20-03-2001	    |	20-03-2001
11	|	Ions Comp's	|	20-03-2001	    |	20-03-2001
12	|	Ion Comp	|	20-03-2001	    |	20-03-2001
13	|	Lamar Ken	|	20-03-2001	    |	20-03-2001
14	|	Lamar       |	20-03-2001	    |	20-03-2001

2nd Table: Company_details
--------------------------------------------------------------------------------------
id	| companyID	 |	city	   |	created_at		|	updated_at
--------------------------------------------------------------------------------------
1	|	10	     |	Alabama    |	20-03-2001	    |	20-03-2001
2	|	11		 |	California |	20-03-2001	    |	20-03-2001
3	|	12     	 |	LA         |	20-03-2001	    |	20-03-2001
4	|	13     	 |	Ohio       |	20-03-2001	    |	20-03-2001
5	|	14	     |	West	   |	20-03-2001	    |	20-03-2001

I want to be my table like this:

1st Table: Companies
-----------------------------------------------------------------------------------
id	|	name		|	created_at		|	updated_at
-----------------------------------------------------------------------------------
10	|	Ions Comps	|	20-03-2001	    |	20-03-2001
13	|	Lamar Ken	|	20-03-2001	    |	20-03-2001

2nd Table: Company_details
--------------------------------------------------------------------------------------
id	| companyID	 |	city	   |	created_at		|	updated_at
--------------------------------------------------------------------------------------
1	|	10	     |	Alabama    |	20-03-2001	    |	20-03-2001
2	|	10		 |	California |	20-03-2001	    |	20-03-2001
3	|	10     	 |	LA         |	20-03-2001	    |	20-03-2001
4	|	13     	 |	Ohio       |	20-03-2001	    |	20-03-2001
5	|	13	     |	West	   |	20-03-2001	    |	20-03-2001

Tray2

4 years ago

Level 73

A MySQL query no, a SQL query maybe, stored procedure definitely.

There is a bit too much logic to do it with a regular SQL query but you can do it with a stored procedure. A multi step query approach might work as well.

Here is a guide on how to create those https://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx

Or you can do it in php if you prefer that.

MohamedTammam

4 years ago

Level 51

Frist to update the companyId column

UPDATE company_details cd 
SET companyId = (
			SELECT MIN(c1.id) 
			FROM company c1
			INNER JOIN company c2
			ON  c1.name = c2.name
			WHERE c2.id = cd.companyId 
		);

Second to delete duplicates

DELETE FROM company c1 
WHERE 
	1 < (SELECT COUNT(*) FROM company c2 WHERE c1.name = c2.name)  
	AND
	c1.id != (SELECT MIN(c1.id) FROM company c2 WHERE c1.name = c2.name)

1 like

princeparaste

4 years ago

Level 1

Thanks @MohamedTammam I made some modification in your first query to achieve what i want but overall your query works. Thanks again. I modified and joined table on LIKE instead of =

UPDATE company_details cd 
SET companyId = (
			SELECT MIN(c1.id) 
			FROM company c1
			INNER JOIN company c2
			ON  c1.name LIKE CONCAT('%', c2.name, '%')
			WHERE c2.id = cd.companyId 
		);

But Your 2nd query to delete the duplicates is giving me error near.

c1  WHERE  1 <

1 like

Please or to participate in this conversation.