0

Original Question

MySQL workbench allows one to define "inserts": rows to be inserted into the database on creation. It does this by adding lines such as

START TRANSACTION;
USE `someDB`;
INSERT INTO `someDB`.`countries` (`name`) VALUES ('South Africa');

COMMIT;

However, if the database, table and entry exists, this throws an error. Creation of tables does not, as workbench uses CREATE IF NOT EXISTS for those. Is there a way to get workbench to insert using INSERT...ON DUPLICATE KEY UPDATE?

Half Solution

Running the script with the force argument:

mysql user=xx password=xx --force < script.sql

Ignores such errors, and is thus a solution in my particular case. However, the actual question of modifying the type of INSERTS still stands (for interest)

See here

Community
  • 1
  • 1
rspencer
  • 2,231
  • 2
  • 17
  • 28
  • Not sure I follow. If this insert happens on database or table creation, how could there be pre-existing rows in any tables? – Mike Brant Apr 07 '15 at 20:15
  • What are you trying to update? The point is that you have a unique key on countries. I expect you're not trying to create duplicates. If it gives you an error, you can always choose to ignore said error (which is how this kind of thing typically works). – John Green Apr 07 '15 at 20:15
  • @MikeBrant It is possible that the table exists, and is populated, hence why the `CREATE IF NOT EXISTS` is important. @john-green: Yes. This suffices. – rspencer Apr 07 '15 at 20:24
  • Would you mind giving us a little more context? Where are you creating the inserts? – Snivs Apr 07 '15 at 20:27
  • @Snivs Not quite sure what you are asking, but the inserts are being created under the "inserts" tab of a table in an ER diagram? – rspencer Apr 07 '15 at 20:28
  • Oh, if you re working on the ER diagram, maybe you could export the inserts into a separate sql script, so you could use INSERT IGNORE or INSERT...ON DUPLICATE KEY UPDATE as you see fit. – Snivs Apr 07 '15 at 20:37
  • It looks like http://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table might work for you. – DDay Apr 07 '15 at 21:14
  • Isn't there a window where you can type in arbitrary SQL? – Rick James Apr 08 '15 at 23:56
  • I am not looking to use SQL that I write, I want it to come from the Inserts I set for each table in the ER diagram. However, I understand if it is not possible. – rspencer Apr 09 '15 at 06:22

0 Answers0