DEV Community: sugawani

DEV Community: sugawani The latest articles on DEV Community by sugawani (@sugawani). https://dev.to/sugawani https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F1330292%2Fb58f3e51-1c45-4c67-b432-acf37978ada5.jpeg DEV Community: sugawani https://dev.to/sugawani en Adding sequence duplicate records in MySQL without a window function sugawani Fri, 08 Mar 2024 09:17:50 +0000 https://dev.to/sugawani/adding-sequence-duplicate-records-in-mysql-without-a-window-function-3k07 https://dev.to/sugawani/adding-sequence-duplicate-records-in-mysql-without-a-window-function-3k07 <h2> Introduction </h2> <p>*I'm learning English now. Please correct my English if I wrote something wrong.</p> <p>For example, suppose you have a table that contains the following data.</p> <div class="table-wrapper-paragraph"><table> <thead> <tr> <th>id</th> <th>group_id</th> <th>name</th> </tr> </thead> <tbody> <tr> <td>1</td> <td>1</td> <td>hoge</td> </tr> <tr> <td>2</td> <td>1</td> <td>hoge</td> </tr> <tr> <td>3</td> <td>2</td> <td>fuga</td> </tr> <tr> <td>4</td> <td>2</td> <td>fuga</td> </tr> <tr> <td>5</td> <td>2</td> <td>fuga</td> </tr> <tr> <td>6</td> <td>2</td> <td>piyo</td> </tr> <tr> <td>7</td> <td>3</td> <td>foo</td> </tr> <tr> <td>8</td> <td>4</td> <td>bar</td> </tr> <tr> <td>9</td> <td>4</td> <td>bar</td> </tr> </tbody> </table></div> <p>For each <code>group_Id</code>, add suffixes to duplicate names as follows.</p> <div class="table-wrapper-paragraph"><table> <thead> <tr> <th>id</th> <th>group_id</th> <th>name</th> </tr> </thead> <tbody> <tr> <td>1</td> <td>1</td> <td>hoge</td> </tr> <tr> <td>2</td> <td>1</td> <td>hoge_2</td> </tr> <tr> <td>3</td> <td>2</td> <td>fuga</td> </tr> <tr> <td>4</td> <td>2</td> <td>fuga_2</td> </tr> <tr> <td>5</td> <td>2</td> <td>fuga_3</td> </tr> <tr> <td>6</td> <td>2</td> <td>piyo</td> </tr> <tr> <td>7</td> <td>3</td> <td>foo</td> </tr> <tr> <td>8</td> <td>4</td> <td>bar</td> </tr> <tr> <td>9</td> <td>4</td> <td>bar_2</td> </tr> </tbody> </table></div> <h2> How to </h2> <p>If you are using MySQL8. <a href="https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html">Window function</a> can be used.<br> but in this article, I will explain the queries that can be used with MySQL5.</p> <h2> All queries </h2> <p>For example, the table name is "test_group".<br> </p> <div class="highlight js-code-highlight"> <pre class="highlight sql"><code><span class="c1">-- Adding sequence duplicate records</span> <span class="k">SET</span> <span class="o">@</span><span class="n">group_id</span> <span class="o">=</span> <span class="k">NULL</span><span class="p">,</span> <span class="o">@</span><span class="n">name</span> <span class="o">=</span> <span class="k">NULL</span><span class="p">,</span> <span class="o">@</span><span class="n">seq</span> <span class="o">=</span> <span class="mi">0</span><span class="p">;</span> <span class="k">CREATE</span> <span class="k">TEMPORARY</span> <span class="k">TABLE</span> <span class="n">tmp_test_group</span> <span class="k">AS</span> <span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span> <span class="n">group_id</span><span class="p">,</span> <span class="n">name</span><span class="p">,</span> <span class="o">@</span><span class="n">seq</span> <span class="p">:</span><span class="o">=</span> <span class="n">IF</span><span class="p">(</span><span class="o">@</span><span class="n">group_id</span> <span class="o">=</span> <span class="n">group_id</span> <span class="k">AND</span> <span class="o">@</span><span class="n">name</span> <span class="o">=</span> <span class="n">name</span><span class="p">,</span> <span class="o">@</span><span class="n">seq</span> <span class="o">+</span> <span class="mi">1</span><span class="p">,</span> <span class="mi">1</span><span class="p">)</span> <span class="k">AS</span> <span class="n">seq</span><span class="p">,</span> <span class="o">@</span><span class="n">group_id</span> <span class="p">:</span><span class="o">=</span> <span class="n">group_id</span> <span class="k">AS</span> <span class="n">hoge</span><span class="p">,</span> <span class="o">@</span><span class="n">name</span> <span class="p">:</span><span class="o">=</span> <span class="n">name</span> <span class="k">AS</span> <span class="n">hoge2</span> <span class="k">FROM</span> <span class="n">test_group</span> <span class="k">WHERE</span> <span class="p">(</span><span class="n">group_id</span><span class="p">,</span> <span class="n">name</span><span class="p">)</span> <span class="k">IN</span> <span class="p">(</span> <span class="k">SELECT</span> <span class="n">group_id</span><span class="p">,</span> <span class="n">name</span> <span class="k">FROM</span> <span class="n">test_group</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">group_id</span><span class="p">,</span> <span class="n">name</span> <span class="k">HAVING</span> <span class="k">count</span><span class="p">(</span><span class="n">group_id</span><span class="p">)</span> <span class="o">></span> <span class="mi">1</span> <span class="k">AND</span> <span class="k">count</span><span class="p">(</span><span class="n">name</span><span class="p">)</span> <span class="o">></span> <span class="mi">1</span> <span class="p">)</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">group_id</span><span class="p">,</span> <span class="n">name</span><span class="p">;</span> <span class="c1">-- Add suffix to name</span> <span class="k">UPDATE</span> <span class="n">tmp_test_group</span> <span class="k">SET</span> <span class="n">name</span> <span class="o">=</span> <span class="n">CONCAT</span><span class="p">(</span><span class="n">name</span><span class="p">,</span> <span class="s1">'_'</span><span class="p">,</span> <span class="n">seq</span><span class="p">)</span> <span class="k">WHERE</span> <span class="n">seq</span> <span class="o"><></span> <span class="mi">1</span><span class="p">;</span> <span class="c1">-- update origin table name</span> <span class="k">UPDATE</span> <span class="k">group</span> <span class="k">AS</span> <span class="n">org</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">tmp_test_group</span> <span class="k">AS</span> <span class="n">tmp</span> <span class="k">ON</span> <span class="n">org</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">tmp</span><span class="p">.</span><span class="n">id</span> <span class="k">SET</span> <span class="n">org</span><span class="p">.</span><span class="n">name</span> <span class="o">=</span> <span class="n">tmp</span><span class="p">.</span><span class="n">name</span> </code></pre> </div> <h2> Explanation </h2> <h3> SELECT duplicate records </h3> <div class="highlight js-code-highlight"> <pre class="highlight sql"><code><span class="k">SELECT</span> <span class="n">group_id</span><span class="p">,</span> <span class="n">name</span> <span class="k">FROM</span> <span class="n">test_group</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">group_id</span><span class="p">,</span> <span class="n">name</span> <span class="k">HAVING</span> <span class="k">count</span><span class="p">(</span><span class="n">group_id</span><span class="p">)</span> <span class="o">></span> <span class="mi">1</span> <span class="k">AND</span> <span class="k">count</span><span class="p">(</span><span class="n">name</span><span class="p">)</span> <span class="o">></span> <span class="mi">1</span> </code></pre> </div> <p>Use <code>GROUP BY</code> and <code>HAVING</code> to get records with duplicate <code>group_id</code> and <code>name</code>.</p> <h3> Adding sequence duplicate records </h3> <div class="highlight js-code-highlight"> <pre class="highlight sql"><code><span class="k">SET</span> <span class="o">@</span><span class="n">group_id</span> <span class="o">=</span> <span class="k">NULL</span><span class="p">,</span> <span class="o">@</span><span class="n">name</span> <span class="o">=</span> <span class="k">NULL</span><span class="p">,</span> <span class="o">@</span><span class="n">seq</span> <span class="o">=</span> <span class="mi">0</span><span class="p">;</span> <span class="k">CREATE</span> <span class="k">TEMPORARY</span> <span class="k">TABLE</span> <span class="n">tmp_test_group</span> <span class="k">AS</span> <span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span> <span class="n">group_id</span><span class="p">,</span> <span class="n">name</span><span class="p">,</span> <span class="o">@</span><span class="n">seq</span> <span class="p">:</span><span class="o">=</span> <span class="n">IF</span><span class="p">(</span><span class="o">@</span><span class="n">group_id</span> <span class="o">=</span> <span class="n">group_id</span> <span class="k">AND</span> <span class="o">@</span><span class="n">name</span> <span class="o">=</span> <span class="n">name</span><span class="p">,</span> <span class="o">@</span><span class="n">seq</span> <span class="o">+</span> <span class="mi">1</span><span class="p">,</span> <span class="mi">1</span><span class="p">)</span> <span class="k">AS</span> <span class="n">seq</span><span class="p">,</span> <span class="o">@</span><span class="n">group_id</span> <span class="p">:</span><span class="o">=</span> <span class="n">group_id</span> <span class="k">AS</span> <span class="n">hoge</span><span class="p">,</span> <span class="o">@</span><span class="n">name</span> <span class="p">:</span><span class="o">=</span> <span class="n">name</span> <span class="k">AS</span> <span class="n">hoge2</span> </code></pre> </div> <p>Use SELECT and variables to check if the current record matches the previous one by <code>group_id, name</code>.<br> Here's how it works.</p> <h4> 1. SELECT first record </h4> <div class="table-wrapper-paragraph"><table> <thead> <tr> <th>id</th> <th>group_id</th> <th>name</th> </tr> </thead> <tbody> <tr> <td>1</td> <td>1</td> <td>hoge</td> </tr> </tbody> </table></div> <ul> <li> <code>@seq = 1</code> <ul> <li>At this point, both <code>@group_id</code> and <code>@name</code> are <code>NULL</code>, so the <code>IF condition</code> evaluates to <code>false</code>, and <code>1</code> is assigned.</li> </ul> </li> <li><code>@group_id = 1</code></li> <li><code>@name = 'hoge'</code></li> </ul> <h4> 2. INSERT to <code>tmp_test_group</code> </h4> <div class="table-wrapper-paragraph"><table> <thead> <tr> <th>id</th> <th>group_id</th> <th>name</th> <th>seq</th> </tr> </thead> <tbody> <tr> <td>1</td> <td>1</td> <td>hoge</td> <td>1</td> </tr> </tbody> </table></div> <h4> 3. SELECT next record </h4> <div class="table-wrapper-paragraph"><table> <thead> <tr> <th>id</th> <th>group_id</th> <th>name</th> </tr> </thead> <tbody> <tr> <td>2</td> <td>1</td> <td>hoge</td> </tr> </tbody> </table></div> <ul> <li> <code>@seq = 2</code> <ul> <li>At this point, <code>@group_id = 1</code> and <code>@name = 'hoge'</code>.</li> <li>Since the currently selected <code>group_id, name</code> match, the <code>IF condition</code> evaluates to <code>true</code>, and <code>@seq + 1</code> is assigned.</li> </ul> </li> <li><code>@group_id = 1</code></li> <li><code>@name = 'hoge'</code></li> </ul> <h4> 4. INSERT to <code>tmp_test_group</code> </h4> <div class="table-wrapper-paragraph"><table> <thead> <tr> <th>id</th> <th>group_id</th> <th>name</th> <th>seq</th> </tr> </thead> <tbody> <tr> <td>1</td> <td>1</td> <td>hoge</td> <td>1</td> </tr> <tr> <td>2</td> <td>1</td> <td>hoge</td> <td>2</td> </tr> </tbody> </table></div> <h4> 5. SELECT next record </h4> <div class="table-wrapper-paragraph"><table> <thead> <tr> <th>id</th> <th>group_id</th> <th>name</th> </tr> </thead> <tbody> <tr> <td>3</td> <td>2</td> <td>fuga</td> </tr> </tbody> </table></div> <ul> <li> <code>@seq = 1</code> <ul> <li>At this point, <code>@group_id = 1</code> and <code>@name = 'hoge</code> </li> <li>Since the currently selected <code>group_id, name</code> is not match, the IF condition evaluates to <code>false</code>, and <code>1</code> is assigned</li> </ul> </li> <li><code>@group_id = 2</code></li> <li><code>@name = 'fuga'</code></li> </ul> <h4> 6. INSERT to <code>tmp_test_group</code> </h4> <div class="table-wrapper-paragraph"><table> <thead> <tr> <th>id</th> <th>group_id</th> <th>name</th> <th>seq</th> </tr> </thead> <tbody> <tr> <td>1</td> <td>1</td> <td>hoge</td> <td>1</td> </tr> <tr> <td>2</td> <td>1</td> <td>hoge</td> <td>2</td> </tr> <tr> <td>3</td> <td>2</td> <td>fuga</td> <td>1</td> </tr> </tbody> </table></div> <p>This process is repeated for each record until there are no more records.</p> <p>💡Point💡<br> Always assign to <code>@seq</code> before <code>@group_id, @name</code>.<br> If you evaluate before @seq, you would lose last selected record.</p> <h3> Add suffix to name </h3> <div class="highlight js-code-highlight"> <pre class="highlight sql"><code><span class="k">UPDATE</span> <span class="n">tmp_test_group</span> <span class="k">SET</span> <span class="n">name</span> <span class="o">=</span> <span class="n">CONCAT</span><span class="p">(</span><span class="n">name</span><span class="p">,</span> <span class="s1">'_'</span><span class="p">,</span> <span class="n">seq</span><span class="p">)</span> <span class="k">WHERE</span> <span class="n">seq</span> <span class="o"><></span> <span class="mi">1</span><span class="p">;</span> </code></pre> </div> <p>I want to create records named <code>hoge, hoge_2, hoge_3...</code>, so I am updating records where <code>seq</code> is greater than 1.</p> <h3> update origin table name </h3> <div class="highlight js-code-highlight"> <pre class="highlight sql"><code><span class="k">UPDATE</span> <span class="k">group</span> <span class="k">AS</span> <span class="n">org</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">tmp_test_group</span> <span class="k">AS</span> <span class="n">tmp</span> <span class="k">ON</span> <span class="n">org</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">tmp</span><span class="p">.</span><span class="n">id</span> <span class="k">SET</span> <span class="n">org</span><span class="p">.</span><span class="n">name</span> <span class="o">=</span> <span class="n">tmp</span><span class="p">.</span><span class="n">name</span> </code></pre> </div> <p>Update the original table's name by joining on id with the temporary table.</p> <h2> FYI </h2> <p>If you are using MySQL8, you can use <a href="https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html">Window function</a> to write this more easily.<br> </p> <div class="highlight js-code-highlight"> <pre class="highlight sql"><code><span class="k">WITH</span> <span class="n">tmp</span> <span class="k">AS</span> <span class="p">(</span> <span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span> <span class="n">group_id</span><span class="p">,</span> <span class="n">name</span><span class="p">,</span> <span class="n">ROW_NUMBER</span><span class="p">()</span> <span class="n">OVER</span> <span class="p">(</span><span class="k">PARTITION</span> <span class="k">BY</span> <span class="n">group_id</span><span class="p">,</span> <span class="n">name</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">id</span><span class="p">)</span> <span class="k">AS</span> <span class="n">seq</span> <span class="k">FROM</span> <span class="n">test_group</span> <span class="k">WHERE</span> <span class="p">(</span><span class="n">group_id</span><span class="p">,</span> <span class="n">name</span><span class="p">)</span> <span class="k">IN</span> <span class="p">(</span> <span class="k">SELECT</span> <span class="n">group_id</span><span class="p">,</span> <span class="n">name</span> <span class="k">FROM</span> <span class="n">test_group</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">group_id</span><span class="p">,</span> <span class="n">name</span> <span class="k">HAVING</span> <span class="k">count</span><span class="p">(</span><span class="n">group_id</span><span class="p">)</span> <span class="o">></span> <span class="mi">1</span> <span class="k">AND</span> <span class="k">count</span><span class="p">(</span><span class="n">name</span><span class="p">)</span> <span class="o">></span> <span class="mi">1</span> <span class="p">)</span> <span class="p">)</span> <span class="k">UPDATE</span> <span class="n">test_group</span> <span class="k">JOIN</span> <span class="n">tmp</span> <span class="k">ON</span> <span class="n">test_group</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">tmp</span><span class="p">.</span><span class="n">id</span> <span class="k">SET</span> <span class="n">test_group</span><span class="p">.</span><span class="n">name</span> <span class="o">=</span> <span class="n">CONCAT</span><span class="p">(</span><span class="n">test_group</span><span class="p">.</span><span class="n">name</span><span class="p">,</span> <span class="s1">'_'</span><span class="p">,</span> <span class="n">tmp</span><span class="p">.</span><span class="n">seq</span><span class="p">)</span> <span class="k">WHERE</span> <span class="n">tmp</span><span class="p">.</span><span class="n">seq</span> <span class="o">></span> <span class="mi">1</span><span class="p">;</span> </code></pre> </div> mysql sql