Databases
StackOverflow2013
Postsdbo
PORemove duplicate data from mysql table accross two columns
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORemove duplicate data from mysql table accross two columns
    primarykey
    data
    text
    <p>I have a MySQL Table which looks like:<br> (unique_id, uid_data1, uid_data2, sorting_data1, sorting_data2)</p> <p>This table is used in a tool, where bidirectional relations weren't supported until now, so the table contains data that looks like (field order according line above):<br> (1, 1212, 2034, 1, 1)<br> (2, 2034, 1212, 1, 1)<br> (3, 4567, 9876, 1, 0)<br> (4, 9876, 4567, 0, 1)</p> <p>The table also contains "single-directed" relations, i.e.<br> (5, 5566, 8899, 1, 9)<br> => no row exists for (?, 8899, 5566, 9, 1)</p> <p>As the tool now supports bidirectional/symmetric relations, I would like to remove the duplicate data from the mysql table - however I'm having some trouble finding an appropriate query to do this.<br> In the example above I would like to delete the rows with the uids 2 and 4 (as their data is already stored in the rows 1 and 3.</p> <p>First, I tried to setup a SELECT-Statement to see, which entries would be deleted.<br> I thought of a JOIN-Query</p> <pre><code>SELECT x.uid, x.uid_link1, x.uid_link2, y.uid_link1 as 'uid_link2', y.uid_link2 as 'uid_link1' FROM tx_sdfilmbase_hilfstab x INNER JOIN tx_sdfilmbase_hilfstab y ON x.uid_link1=y.uid_link2 AND x.uid_link2=y.uid_link1 WHERE ??? ORDER BY x.uid_link1, x.uid_link2 </code></pre> <p>However I'm stuck at the point where I have to tell MySQL to only select "half portion" of the records.<br> Any suggestions on how to do this?</p> <p>P.S. Deleting each single record manually in the table isn't an option, as the table contains several thousand rows ;-)</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USStefan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PORemove duplicate data from mysql table accross two columns
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PORemove duplicate data from mysql table accross two columns
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThis isn't going to be syntactically accurate, but something like `delete from tx_sdfilmbase_hilfstab where uid_link2 in (select uid_link1 from tx_sdfilmbase_hilfstab)` might work...
      singulars
      1. PORemove duplicate data from mysql table accross two columns
      1. USChris Thompson
    2. CObut then I might be deleting rows with no double entry, like: (1122, 2233), (2233, 1122), (5566, 1122) => here both (2233, 1122) and (5566, 1122) would be deleted, although only (2233, 1122) should be deleted, as (5566, 1122) has no double entry
      singulars
      1. PORemove duplicate data from mysql table accross two columns
      1. USStefan
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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