1

In mysql when I'm doing

# fr-FR.sql
INSERT INTO table (resource_key, resource_value, locale) VALUES 
    ("GEN.YES", "Oui", "fr-FR"),
    ("GEN.NO", "Non", "fr-FR"),
    ("GEN.MAYBE", "Peut-être", "fr-FR")
ON DUPLICATE KEY UPDATE `resource_value` = VALUES(`resource_value`);

# en-US.sql
INSERT INTO table (resource_key, resource_value, locale) VALUES 
    ("GEN.YES", "Yes", "en-US"),
    ("GEN.NO", "No", "en-US"),
    ("GEN.MAYBE", "Maybe", "en-US")
ON DUPLICATE KEY UPDATE `resource_value` = VALUES(`resource_value`);

Here, 'resource_key' and 'locale' are the primary key. 'locale' is always the same in one file. But differ from file to file (can't set as default value);

This query works. It insert each row and if a label for a given resource_key/locale pair exists, it will update its associated resource_value

Is it possible to insert same value in each row for a given column. There is thousands of lines in different files (added continuously) and always typing the same string in each row is annoying.

4
  • Have you considered using a variable (which would still have to be inserted for each row), or setting a default for the column at the table level (which would then hold true until/unless the table definition was altered)? I assume these would fail to meet your need (for the reasons I've stated), but it never hurts to check.
    – RDFozz
    Aug 31, 2017 at 17:34
  • I see 6 "random_str". Are you saying that pairs are actually equal? Or one column has all the same random string? Please clarify, preferably by changing the 'example'.
    – Rick James
    Aug 31, 2017 at 21:53
  • Same problem with variables. Can't set default (see edited question). Pairs aren't equals; I updated my question so it's much more clear I think. Sep 1, 2017 at 8:05
  • Nobody has any idea ? Sep 28, 2017 at 9:21

1 Answer 1

1

I am surely too late for you, but maybe somebody will have similar question in the future.

A way how to accomplish that is by creating temporary table #temptable, and while copying data to actual table, add constant column:

# en-US.sql
INSERT INTO #temptable (resource_key, resource_value) VALUES 
    ("GEN.YES", "Yes"),
    ("GEN.NO", "No"),
    ("GEN.MAYBE", "Maybe")
ON DUPLICATE KEY UPDATE `resource_value` = VALUES(`resource_value`);

INSERT INTO table (resource_key, resource_value, locale) VALUES 
    (SELECT t.*, "en-US" FROM #temptable AS t);
1
  • Hey thanks for creating an account just to answer me ! Really appreciate it. You're not too late, i just ignored that until now but i'll update it now I have a better solution. Thanks a lot ! May 16, 2019 at 14:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.