asked
Sql (mariadb) delete duplicate name and update other tables
I want to delete the duplicate name from Table: attribute_lang. Also, update product_attribute table with id_attribute. Please help on sql. Table : attribute_lang id_attribute,name 1,15 2,16 3,15 -> remove 4,16 -> remove Table : product_attribute id_product,id_attribute 12,1 13,2 14,3 -> change id_attribute to 1 15,4 -> change id_attribute to 2
http://www.sqlfiddle.com/#!9/903455 -- -- Table structure for table `attribute_lang` -- DROP TABLE IF EXISTS `attribute_lang`; CREATE TABLE IF NOT EXISTS `attribute_lang` ( `id_attribute` int(22) NOT NULL, `name` varchar(44) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `attribute_lang` -- INSERT INTO `attribute_lang` (`id_attribute`, `name`) VALUES (1, '15'), (2, '16'), (3, '15'), (4, '16'); -- -------------------------------------------------------- -- -- Table structure for table `product_attribute` -- DROP TABLE IF EXISTS `product_attribute`; CREATE TABLE IF NOT EXISTS `product_attribute` ( `id_product` int(11) NOT NULL, `id_attribute` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `product_attribute` -- INSERT INTO `product_attribute` (`id_product`, `id_attribute`) VALUES (12, 1), (13, 2), (14, 3), (15, 4);
I am not sure why, but the SQL fiddle does not let me use some of the SQL features, even though I know that they are available in the latest version of MySQL. I guess it checks the syntax against somewhat older version. Because of it, the solution uses just the most primitive, admittedly awkward constructs to make sure that it works with older versions. Regardless of whether there is a formal relationship between the tables or not, it is still the best to first take care of updating the child records in the **product\_arrtibute** table before deleting the duplicates from the **attribute\_lang** catalog. It appears that the logic in question is pretty straightforward, i.e. retain the the lowest ID for those cases when there is more than one ID for the same name. Here is the update statement (to update the child records with correct ID values) followed by delete statement to get rid of the duplicates in the **attribute\_lang** update product_attribute p inner join ( select a.id_attribute, ( select min(id_attribute) from attribute_lang where `name` = a.`name` ) corrected_id from attribute_lang a ) as c on p.id_attribute = c.id_attribute set p.id_attribute = c.corrected_id; delete from attribute_lang where id_attribute in ( select id_attribute from ( select a.id_attribute, ( select min(id_attribute) corrected_id from attribute_lang where `name` = a.`name` ) corrected_id from attribute_lang a ) t where t.id_attribute t.corrected_id ); Just to show how much more elegant the delete statement would be in T-SQL, here is the same statement which would work in any version of SQL Server starting from 2005: ;with dups as ( select id_attribute, row_number() over (partition by [name] order by id_attribute) n from attribute_lang ) delete dups where n > 1; go Hope this helps. Oleg
18 People are following this question.
