MySQL - Insert Multiple Records or Update Multiple Records If Exists
<p>I want to <strong><em>INSERT</em></strong> a new record into my database if it is no exists, otherwise it will <strong><em>UPDATE</em></strong> those existing records.</p>

<p>I did search on Stackoverflow, but none of the result can solve my issue.</p>

<ul>
<li><a href="https://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update?rq=1">&quot;INSERT IGNORE&quot; vs &quot;INSERT ... ON DUPLICATE KEY UPDATE&quot;</a></li>
<li><a href="https://stackoverflow.com/questions/2714587/mysql-on-duplicate-key-update-for-multiple-rows-insert-in-single-query">MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query</a></li>
</ul>

<p><strong>TABLE STRUCTURE</strong>
<a href="https://i.stack.imgur.com/8Fscq.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8Fscq.png" alt="enter image description here"></a></p>

<p><strong>SCENARIO</strong></p>

<p>There are a lot of records inside <em>Purchase Details</em> table. So the only <strong>Unique ID</strong> is only <em>purchase_id</em>. </p>

<p>I want to update the <em>amount</em> which have the same <em>product_id</em>. Refer to the table.</p>

<p><a href="https://i.stack.imgur.com/xjPSG.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/xjPSG.png" alt="enter image description here"></a></p>

<p>Below is the SQL Query I have tried so far.</p>

<pre><code>INSERT INTO `purchase_details` (`product_id`, `amount`)
VALUES (1583, 0)
ON DUPLICATE KEY UPDATE amount = 0
</code></pre>

<p>The query shows <strong>1 row affected</strong>.</p>

<p>But total has <strong>146 rows</strong> which mean this query is not working.</p>

<p><strong>PROBLEM</strong></p>

<ol>
<li><strong>ON DUPLICATE KEY UPDATE</strong> only allow to inserted a row that would cause a duplicate value in a UNIQUE index or PRIMARY KEY</li>
</ol>