MySQL: INSERT to INSERT ... ON DUPLICATE UPDATE

script to convert MySQL dumps from INSERT statements to INSERT ... ON DUPLICATE UPDATE statements. (Converted a Fiddle that changed INSERT into UPDATE. For limitations, See SO: https://stackoverflow.com/a/55337940/5411817)

by Sheryl Hohman

HTML

<div style="display: block;">
  <label for="query" id="ruleslabel">Input INSERT query:</label><br>
  
  <!-- Populate input box with a sample Query -->
  <textarea id="query" rows="10">INSERT INTO `devices`
    (`name`, `idGroup1`, `idGroup2`, `label`) 
VALUES
    ('3703-001', 16, 5, 'Meter BB #1');
    
INSERT INTO `devices`
    (`name`, `idGroup1`, `idGroup2`, `label`) 
VALUES
    ('3703-002', 12, 8, 'Meter CC #2'),
    ('3703-003', 12, 0, 'Meter #3'),
    ('3703-004', 12, 24, 'Meter building F');
    </textarea>
</div>

<button id="btn">Convert</button>

<div style="display: block;">
  <label for="out" id="ruleslabel">Output UPDATE query:</label><br>
  <textarea id="out" rows="10"></textarea>
</div>

CSS

textarea{
  border:1px solid #999999;
  width:100%;
  margin:5px 0;
  padding:3px;
}
button {
	margin:10px;
}

JavaScript

$(document).ready(function() {
	  $('#btn').click(function() {
      
      // to store the output result
      let out_sh = "";
      
	    //const insertQuery = $('#query').val();
	    const userInput = $('#query').val();
      // If query is written as severan queries (single line inserts rather than multi row inserts in a single statement, then need to loop through all the statements).
      // IF there is more than 1 semicolon, or maybe better yet, more than one
      //	INSERT INTO statement..
      // I'll do a split on ; instead ! Cus I want to KEEP the INSERT INTO portion
      //	and I want to get rid of the ; anyway
      // NO, this is bad. ANY query containing a ; in its data would cause this to break
      //const insertQueries = userInput.split(';');
      //const insertQueries = userInput.trim().split('INSERT INTO ');
      //  do a split on INSERT only, I'm Using INTO to do another split later on.
      //   Note this is terrible hack.
      //  WORSE, That I dp splits on parens and commas!!!
      //		Wont work on REAL data !!!
      const insertQueries = userInput.trim().split('INSERT ');

// delete the last part, if it does not contain an INSERT statement
      //	 multiline queries should end in a ;
      //   however, if there is only a single statement, the user may not have an ending ; (ie MySQL Workbench Snippets by default do not include ;)
/*       if (true) {}
      console.log('num insertQueries: ', insertQueries.length);
      console.log('first: ', insertQueries[0]);
      console.log('[last]: ', insertQueries[insertQueries.length-1]);
      console.log('the insertQueries: ', insertQueries);
*/
      // check endpoints of split, if the "query is empty delete it
      // generally will be first element if split on INSERT INTO
      // or Last element (if split on semicolon - bad idea b/c data has semicolons)
      // Could do filter instead, and do something more robust,
      // would rather just DELETE the bad input queries.
     ...