How to remove currencies having count greater than 1 in mysql?

Question

I have a table called currency_country that holds all currencie. Some Europe countries have old currencies as well. I want to remove all non EUR currencies from countries that have a EUR currency?

I have tried these way but didnt help !

DELETE FROM `currency_country`
WHERE currency_code NOT IN (
  SELECT currency_code
  FROM (
    SELECT currency_code
    FROM `currency_country`
    WHERE currency_code = 'EUR'
  ) temp
);

and this

DELETE
FROM
   `currency_country` 
   LEFT JOIN
      country 
      on `currency_country`.`country_id` = country.`id` 
WHERE
   EXISTS 
   (
      SELECT
         * 
      FROM
         `currency_country` 
      WHERE
         EXISTS 
         (
            SELECT
               * 
            FROM
               `currency_country` 
            WHERE
               `currency_code` = 'EUR' 
         )
   )
   AND currency_code != 'EUR' 
   AND country.name IN 
   (
      'Austria',
      'Belgium',
      'Cyprus',
      'Netherlands',
      'Estonia',
      'Finland',
      'France',
      'Germany',
      'Greece',
      'Ireland',
      'Italy',
      'Latvia',
      'Lithuania',
      'Luxembourg',
      'Malta',
      'Monaco',
      'Portugal',
      'San Marino',
      'Slovakia',
      'Slovenia',
      'Spain'
   )
;

`DELETE FROM currency_country WHERE country_id IN (SELECT country_id FROM currency_country WHERE currency_code = 'EUR') AND currency_code <> 'EUR'` — ctwheels, Aug 22 '17 at 15:22

LP154 · Accepted Answer · 2017-08-22T15:59:22.770

1

Have you tried something like this :

DELETE FROM c1 
USING currency_country AS c1
WHERE c1.currency_code != 'EUR' 
AND EXISTS (
    SELECT 1 
    FROM (SELECT * FROM currency_country) AS c2 
    WHERE c2.currency_code = 'EUR' 
    AND c1.country_id = c2.country_id
);

Self explanatory, you delete every currency that is not 'EUR' and where a currency 'EUR' exists for the same country.

edited Aug 22 '17 at 15:59

answered Aug 22 '17 at 15:20

LP154

1,467
9
16

2

The first `Where` condition should be `WHERE c1.currency_code != 'EUR '`, I guess – KubiRoazhon Aug 22 '17 at 15:22
Yes, typo sorry – LP154 Aug 22 '17 at 15:26
for this one I get this error **You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'c1 WHERE c1.currency_code != 'EUR' AND EXISTS ( SELECT 1 FROM `curren' at line 1** – Ahmed Abdulrahman Aug 22 '17 at 15:43
I edited, try this version. The one I posted before used PostGreSQL syntax, my bad – LP154 Aug 22 '17 at 15:56
This time I get this Error I dont know why it happens? **You can't specify target table 'c1' for update in FROM clause** – Ahmed Abdulrahman Aug 22 '17 at 15:57
Another edit to try, thanks to this : https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause/14302701#14302701 If it doesn't work, try with `SET optimizer_switch = 'derived_merge=off';` before – LP154 Aug 22 '17 at 15:59

waterloomatt · Answer 2 · 2017-08-22T16:20:49.563

1

Another method. Find the country IDs for all EUR and then delete all the non-EUR currencies.

DELETE FROM currency_country
WHERE country_id IN (
    SELECT country_id
    FROM currency_country
    WHERE currency_code = 'EUR')
AND currency_code <> 'EUR';

EDIT

Please try this.

DELETE FROM currency_country
WHERE country_id IN (
    SELECT country_id
    FROM (SELECT * FROM currency_country) AS something
    WHERE currency_code = 'EUR')
AND currency_code <> 'EUR';

edited Aug 22 '17 at 16:20

answered Aug 22 '17 at 15:23

waterloomatt

3,662
1
19
25

It says **You can't specify target table 'currency_country' for update in FROM clause** – Ahmed Abdulrahman Aug 22 '17 at 15:40
Try your_db_name.currency_country. – KubiRoazhon Aug 22 '17 at 15:43
Working for you now? – waterloomatt Aug 22 '17 at 18:52

How to remove currencies having count greater than 1 in mysql?

2 Answers2