We have a plugin, which imports an XML file from an FTP server into the database. This XML is further processed, and data is inserted into the database tables. These rows are added to the “pricing” table (with help of the package w 10k+ rows), where prices can be set. My problem is that duplicate values often come through during the import. The system processes them, but during pairing, only the first match is taken into table.

To simplify, there is a table.. the important columns are: the ID, the column “SITE”, and the column LINKED, where the ID is attached with an exclamation mark (!) to the PK.

How can I find the pairs and filter out those that don’t have a pair or those that are linked to the same ID more than once?

After this, I would like to make a procedure to update the column to fix the pairing – with the correct ID.

table:

I have colored the pairs to make it easier to visualize and understand what the issue is. Here you can see, that SITE: 292 ID: 4 is not paired to ID:6.

I have no IDEA, how can i link the correct.. i can find the not good one-s, with sub-select:

select * from pricing p where p.PK='3452400012026' 
 and p.site='292' 
 and 
 (select count(*) from pricing po 
  where po.PK = p.PK and 
   po.LINKED=p.PK||'!'||p.ID) != 1

So if it’s not 1, it’s problematic…