CODEWITHSUNDEEP
mysqlinsert
user2465936
user2465936

Reputation: 1040

Mysql INSERT IGNORE if in particular row values in two columns already exists

CurrencyAbbreviation | CurrencyRate | DateOfCurrencyRate

  AUD                |  1.1         |   2013-01-01 
  USD                |  1.1         |   2013-01-01 
  EUR                |  1.1         |   2013-01-01

Want to prevent insert the same currency with the same date.

Want to prevent this

CurrencyAbbreviation | CurrencyRate | DateOfCurrencyRate

  AUD                |  1.1         |   2013-01-01 
  AUD                |  1.1         |   2013-01-01

If set unique either CurrencyAbbreviation or DateOfCurrencyRate then for example could not insert either AUD | 2013-01-02 or USD | 2013-01-03

Tried REPLACE to check, but the same result and suppose REPLACE is not good for the situation.

Now see some solution to create separate table for each currency, but not good, because there are ~ 40+ currencies and INSERT `SELECT`` multiple tables waste of resources

Please, advice solution.

Thinking, seems need to create additional column that contains currency and date and set the column as unique. Seems at the moment it is the best solution I know

Upvotes: 2

Views: 637

Answers (1)

Mchl
Mchl

Reputation: 62359

Create a UNIQUE index spanning these two columns

CREATE 
    UNIQUE INDEX currency_date 
    ON yourTableName (CurrencyAbbreviation, DateOfCurrencyRate)

Upvotes: 3

Related Questions