ALTER TABLE `banksfinders`.`creditcardstypes` ADD COLUMN `showonfrontscreen` TINYINT NULL AFTER `idcardtype`, ADD COLUMN `showfrontscreenseqno` INT NULL AFTER `showonfrontscreen`; UPDATE `banksfinders`.`cardtypes` SET `useascategory` = '0' WHERE (`idcardtype` = '12'); UPDATE `banksfinders`.`cardtypes` SET `useascategory` = '0' WHERE (`idcardtype` = '16'); UPDATE `banksfinders`.`cardtypes` SET `useascategory` = '0' WHERE (`idcardtype` = '18'); INSERT INTO `banksfinders`.`cardtypes` ( `cardtypecode`, `cardtypedesc`, `useascategory`) VALUES ('BDINOF', 'Best Travel Card', '1'); WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY creditcardsname ORDER BY idcreditcards) AS rn FROM creditcards ) select * from cte WHERE idcreditcards IN (SELECT idcreditcards FROM cte WHERE rn > 1); -- DELETE FROM creditcardsfetures WHERE idcreditcards IN (SELECT idcreditcards FROM cte WHERE rn > 1); DELETE FROM creditcards WHERE idcreditcards IN (SELECT idcreditcards FROM cte WHERE rn > 1); select *,(select idcreditcards from creditcards cc Where TRIM(cf.CARD_name) = TRIM(cc.creditcardsname)) from fetures_transformed cf where Shrt_Features is not null -- delete from creditcardstypes; insert into creditcardstypes(`idcreditcards`,`showonfrontscreen`,`showfrontscreenseqno`,`idcardtype`) select idcreditcards,1 showonfrontscreen,1 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best CashBack Card' ) idcardtype from creditcards where creditcardsname like '%fab cash%' union all select idcreditcards,1 showonfrontscreen,2 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best CashBack Card' ) idcardtype from creditcards where creditcardsname like '%DUO%' union all select idcreditcards,1 showonfrontscreen,3 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best CashBack Card' ) idcardtype from creditcards where creditcardsname like '%super saver%' union all select idcreditcards,1 showonfrontscreen,4 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best CashBack Card' ) idcardtype from creditcards where creditcardsname like '%dubai first cash%' union all select idcreditcards,1 showonfrontscreen,5 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best CashBack Card' ) idcardtype from creditcards where creditcardsname like '%ultra%' union all select idcreditcards,1 showonfrontscreen,1 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Movieoffer Card' ) idcardtype from creditcards where creditcardsname like '%DUO%' union all select idcreditcards,1 showonfrontscreen,2 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Movieoffer Card' ) idcardtype from creditcards where creditcardsname like '%CBD Visa Smiles Signature Credit Card%' union all select idcreditcards,1 showonfrontscreen,3 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Movieoffer Card' ) idcardtype from creditcards where creditcardsname like '%Mashreq Solitaire Credit card%' union all select idcreditcards,1 showonfrontscreen,4 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Movieoffer Card' ) idcardtype from creditcards where creditcardsname like '%FAB Cashback Credit Card%' union all select idcreditcards,1 showonfrontscreen,5 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Movieoffer Card' ) idcardtype from creditcards where creditcardsname like '%Deem Platinum Card%' union all select idcreditcards,1 showonfrontscreen,1 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Dining Offer Card' ) idcardtype from creditcards where creditcardsname like '%Mashreq Noon Credit Card%' union all select idcreditcards,1 showonfrontscreen,2 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Dining Offer Card' ) idcardtype from creditcards where creditcardsname like '%ENBD noon One Visa Credit card%' union all select idcreditcards,1 showonfrontscreen,3 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Dining Offer Card' ) idcardtype from creditcards where creditcardsname like '%FAB Rewards Indulge Card%' union all select idcreditcards,1 showonfrontscreen,4 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Dining Offer Card' ) idcardtype from creditcards where creditcardsname like '%CBD Visa Infinite Credit Card%' -- union all -- select idcreditcards,1 showonfrontscreen,5 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Dining Offer Card' ) idcardtype from creditcards where creditcardsname like '%Deem Platinum Card%' union all select idcreditcards,1 showonfrontscreen,1 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Skywords Card' ) idcardtype from creditcards where creditcardsname like '%Emirates NBD Skywards Infinite Credit Card%' union all select idcreditcards,1 showonfrontscreen,2 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Skywords Card' ) idcardtype from creditcards where creditcardsname like '%Emirates Islamic Skywards Infinite Credit Card%' union all select idcreditcards,1 showonfrontscreen,3 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Skywords Card' ) idcardtype from creditcards where creditcardsname like '%HSBC Emirates Skywards Infinite Credit Card%' -- union all -- select idcreditcards,1 showonfrontscreen,4 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Skywords Card' ) idcardtype from creditcards where creditcardsname like '%CBD Visa Infinite Credit Card%' -- union all -- select idcreditcards,1 showonfrontscreen,5 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Skywords Card' ) idcardtype from creditcards where creditcardsname like '%Deem Platinum Card%' union all select idcreditcards,1 showonfrontscreen,1 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Travel Card' ) idcardtype from creditcards where creditcardsname like '%Emirates NBD Skywards Infinite Credit Card%' union all select idcreditcards,1 showonfrontscreen,2 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Travel Card' ) idcardtype from creditcards where creditcardsname like '%FAB Travel Card world Elite%' union all select idcreditcards,1 showonfrontscreen,3 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Travel Card' ) idcardtype from creditcards where creditcardsname like '%RAK Bank World Credit Card%' -- union all -- select idcreditcards,1 showonfrontscreen,4 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Travel Card' ) idcardtype from creditcards where creditcardsname like '%Emirates NBD Skywards Infinite Credit Card%' -- union all -- select idcreditcards,1 showonfrontscreen,5 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Travel Card' ) idcardtype from creditcards where creditcardsname like '%Deem Platinum Card%' -------------------------------------------------------------------------------------------- select idcreditcards,1 showonfrontscreen,1 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best CashBack Card' ) idcardtype from creditcards where creditcardsname like '%fab cash%' union all select idcreditcards,1 showonfrontscreen,2 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best CashBack Card' ) idcardtype from creditcards where creditcardsname like '%DUO%' union all select idcreditcards,1 showonfrontscreen,3 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best CashBack Card' ) idcardtype from creditcards where creditcardsname like '%super saver%' union all select idcreditcards,1 showonfrontscreen,4 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best CashBack Card' ) idcardtype from creditcards where creditcardsname like '%dubai first cash%' union all select idcreditcards,1 showonfrontscreen,5 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best CashBack Card' ) idcardtype from creditcards where creditcardsname like '%ultra%' ---------------------------------------------------------------------------------------------------------------------------- select idcreditcards,1 showonfrontscreen,1 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Movieoffer Card' ) idcardtype from creditcards where creditcardsname like '%DUO%' union all select idcreditcards,1 showonfrontscreen,2 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Movieoffer Card' ) idcardtype from creditcards where creditcardsname like '%CBD Visa Smiles Signature Credit Card%' union all select idcreditcards,1 showonfrontscreen,3 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Movieoffer Card' ) idcardtype from creditcards where creditcardsname like '%Mashreq Solitaire Credit card%' union all select idcreditcards,1 showonfrontscreen,4 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Movieoffer Card' ) idcardtype from creditcards where creditcardsname like '%FAB Cashback Credit Card%' union all select idcreditcards,1 showonfrontscreen,5 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Movieoffer Card' ) idcardtype from creditcards where creditcardsname like '%Deem Platinum Card%' ---------------------------------------------------------------------------------------------------------------------------- select idcreditcards,1 showonfrontscreen,1 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Dining Offer Card' ) idcardtype from creditcards where creditcardsname like '%Mashreq Noon Credit Card%' union all select idcreditcards,1 showonfrontscreen,2 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Dining Offer Card' ) idcardtype from creditcards where creditcardsname like '%ENBD noon One Visa Credit card%' union all select idcreditcards,1 showonfrontscreen,3 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Dining Offer Card' ) idcardtype from creditcards where creditcardsname like '%FAB Rewards Indulge Card%' union all select idcreditcards,1 showonfrontscreen,4 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Dining Offer Card' ) idcardtype from creditcards where creditcardsname like '%CBD Visa Infinite Credit Card%' -- union all -- select idcreditcards,1 showonfrontscreen,5 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Dining Offer Card' ) idcardtype from creditcards where creditcardsname like '%Deem Platinum Card%' ---------------------------------------------------------------------------------------------------------------------------- select idcreditcards,1 showonfrontscreen,1 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Skywords Card' ) idcardtype from creditcards where creditcardsname like '%Emirates NBD Skywards Infinite Credit Card%' union all select idcreditcards,1 showonfrontscreen,2 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Skywords Card' ) idcardtype from creditcards where creditcardsname like '%Emirates Islamic Skywards Infinite Credit Card%' union all select idcreditcards,1 showonfrontscreen,3 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Skywords Card' ) idcardtype from creditcards where creditcardsname like '%HSBC Emirates Skywards Infinite Credit Card%' -- union all -- select idcreditcards,1 showonfrontscreen,4 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Skywords Card' ) idcardtype from creditcards where creditcardsname like '%CBD Visa Infinite Credit Card%' -- union all -- select idcreditcards,1 showonfrontscreen,5 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Skywords Card' ) idcardtype from creditcards where creditcardsname like '%Deem Platinum Card%' ---------------------------------------------------------------------------------------------------------------------------- select idcreditcards,1 showonfrontscreen,1 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Travel Card' ) idcardtype from creditcards where creditcardsname like '%Emirates NBD Skywards Infinite Credit Card%' union all select idcreditcards,1 showonfrontscreen,2 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Travel Card' ) idcardtype from creditcards where creditcardsname like '%FAB Travel Card world Elite%' union all select idcreditcards,1 showonfrontscreen,3 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Travel Card' ) idcardtype from creditcards where creditcardsname like '%RAK Bank World Credit Card%' -- union all -- select idcreditcards,1 showonfrontscreen,4 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Travel Card' ) idcardtype from creditcards where creditcardsname like '%Emirates NBD Skywards Infinite Credit Card%' -- union all -- select idcreditcards,1 showonfrontscreen,5 showfrontscreenseqno , (select idcardtype from cardtypes c where c.cardtypedesc='Best Travel Card' ) idcardtype from creditcards where creditcardsname like '%Deem Platinum Card%' ----------------------------------------------------------------------------------------------------------------------------