StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to update multiple duplicates with different values on the same table?
text
Body
copied!<p>The table I am dealing with has multiple rows which have the same values for <code>lat</code> and <code>lon</code>. The example shows that <code>1</code>, <code>3</code>, <code>5</code> have the same location but the <code>name</code> attribute differs. The <code>hash</code> is built from <code>name</code>, <code>lat</code> and <code>lon</code> and differs therefore.</p> <pre class="lang-none prettyprint-override"><code>BEFORE: id | name | lat | lon | flag | hash ----+------+-----+-----+------+------ 1 | aaa | 16 | 48 | 0 | 2cd <-- duplicate 2 | bbb | 10 | 22 | 0 | 3fc 3 | ccc | 16 | 48 | 0 | 8ba <-- duplicate 4 | ddd | 10 | 23 | 0 | c33 5 | eee | 16 | 48 | 0 | 751 <-- duplicate </code></pre> <p>I need to identify "duplicates" within this table and want to assign the flag <code>1</code> (<em>primary</em>) to one of them and the flag <code>2</code> (<em>secondary</em>) to the others. It is not important which "duplicate" is flagged as <em>primary</em>.</p> <pre class="lang-none prettyprint-override"><code> AFTER: id | name | lat | lon | flag | hash ----+------+-----+-----+------+------ 1 | aaa | 16 | 48 | 1 | 2cd <-- updated 2 | bbb | 10 | 22 | 0 | 3fc 3 | ccc | 16 | 48 | 2 | 8ba <-- updated 4 | ddd | 10 | 23 | 0 | c33 5 | eee | 16 | 48 | 2 | 751 <-- updated </code></pre> <p>I started experimenting with <code>INNER JOIN</code> <a href="https://stackoverflow.com/a/38578/356895">inspired by this post</a> and this <a href="http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html" rel="nofollow noreferrer">visual description</a>. With this I am able to assign the same flag to all duplicates.</p> <pre class="lang-sql prettyprint-override"><code>UPDATE table t1 INNER JOIN table_name t2 ON t1.lat = t2.lat AND t1.lon = t2.lon AND t1.hash != t2.hash SET t1.flag = 2; </code></pre> <p>I also tested <code>LEFT OUTER JOIN</code> with <code>WHERE t2.id IS NULL</code> which could work when there are only two rows. However, I cannot think my head off how a <code>JOIN</code> should work with <strong>more then two duplicates</strong>. Mark Harrison also assumes <em>"that you're joining on columns with no duplicates"</em> <a href="https://stackoverflow.com/a/38578/356895">at the beginning of his post</a> which sound as if this is not a good idea.</p> <p>I am using MySQL if this is of interest.</p>

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