StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Both responses have possibilities. Just to expand on your options a little ..</p> <h2>Option #1</h2> <p><em>IF</em> mySQL supports some sort of hashing, <em>on a per row basis</em>, you could use a variation of <a href="https://stackoverflow.com/a/10698350/104223">comodoro's suggestion</a> to avoid hard deletes.</p> <p><strong>Identify Changed</strong></p> <p>To identify changes, do an inner join on the primary key and check the hash values. If they are different, the product was changed and should be updated:</p> <pre><code> UPDATE Products p INNER JOIN Products_Temp tmp ON tmp.ProductID = p.ProductID SET p.ProductName = tmp.ProductName , p.Stock = tmp.Stock , ... , p.DateLastChanged = now() , p.IsDiscontinued = 0 WHERE tmp.TheRowHash <> p.TheRowHash </code></pre> <p><strong>Identify Deleted</strong></p> <p>Use a simple outer join to identify records that do not exist in the temp table, and flag them as "deleted"</p> <pre><code> UPDATE Products p LEFT JOIN Products_Temp tmp ON tmp.ProductID = p.ProductID SET p.DateLastChanged = now() , p.IsDiscontinued = 1 WHERE tmp.ProductID IS NULL </code></pre> <p><strong>Identify New</strong></p> <p>Finally, use a similar outer join to insert any "new" products. </p> <pre><code> INSERT INTO Products ( ProductName, Stock, DateLastChanged, IsDiscontinued, .. ) SELECT tmp.ProductName, tmp.Stock, now() AS DateLastChanged, 0 AS IsDiscontinued, ... FROM Products_Temp tmp LEFT JOIN Products p ON tmp.ProductID = p.ProductID WHERE p.ProductID IS NULL </code></pre> <h2>Option #2</h2> <p>If per row hashing is not feasible, an alternate approach is a variation of <a href="https://stackoverflow.com/a/10703239/104223">Sharondio's suggestion</a>. </p> <p>Add a "status" column to the temp table and flag all imported records as "new", "changed" or "unchanged" through a series of joins. (The default should be "changed"). </p> <p><strong>Identify UN-Changed</strong></p> <p>First use an inner join, on all fields, to identify products that have NOT changed. (Note, if your table contains any nullable fields, remember to use something like <code>coalesce</code> Otherwise, the results may be skewed because <code>null</code> values are not equal to anything.</p> <pre><code> UPDATE Products_Temp tmp INNER JOIN Products p ON tmp.ProductID = p.ProductID SET tmp.Status = 'Unchanged' WHERE p.ProductName = tmp.ProductName AND p.Stock = tmp.Stock ... </code></pre> <p><strong>Identify New</strong></p> <p>Like before, use an outer join to identify "new" records. </p> <pre><code> UPDATE Products_Temp tmp LEFT JOIN Products p ON tmp.ProductID = p.ProductID SET tmp.Status = 'New' WHERE p.ProductID IS NULL </code></pre> <p>By process of elimination, all other records in the temp table are "changed". Once you have calculated the statuses, you can update the Products table:</p> <pre><code> /* update changed products */ UPDATE Products p INNER JOIN Products_Temp tmp ON tmp.ProductID = p.ProductID SET p.ProductName = tmp.ProductName , p.Stock = tmp.Stock , ... , p.DateLastChanged = now() , p.IsDiscontinued = 0 WHERE tmp.status = 'Changed' /* insert new products */ INSERT INTO Products ( ProductName, Stock, DateLastChanged, IsDiscontinued, .. ) SELECT tmp.ProductName, tmp.Stock, now() AS DateLastChanged, 0 AS IsDiscontinued, ... FROM Products_Temp tmp WHERE tmp.Status = 'New' /* flag deleted records */ UPDATE Products p LEFT JOIN Products_Temp tmp ON tmp.ProductID = p.ProductID SET p.DateLastChanged = now() , p.IsDiscontinued = 1 WHERE tmp.ProductID IS NULL </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload